Strange behaviour updating field in a CASE with a CTE

Question

Hi all,

I'm going crazy trying to understand the behavior of an update. The SELECT returns the value correctly to me (before the update). Here are the codes:

Values of fields

Real Update

	UPDATE a
	SET
	a.[IsRetail] = 
			CASE 
				WHEN a.[NumeroDipendentiCalc] < 250 AND a.[FatturatoCalc] <= 50000000.0 AND a.[TotaleAttivoCalc] < 43000000.0 AND ec.[Esposizione] <= 1000000 THEN 1
				ELSE 0
			END
	FROM #anagrafiche a
	JOIN EsposizioneCTE ec 
		ON a.[IdAnagrafica] = ec.[IdAnagrafica]

The Debug select..

SELECT
	[IsRetail] = 
		CASE 
			WHEN 10 < 250 AND 3607.00 <= 50000000.0 AND 42999999.99 < 43000000.0 AND 17080.49 <= 1000000 THEN 1
			ELSE 0
		END

from the DEBUG but also with a SELECT in substitution of the real statement... I got "1" the correct value.

During the real UPDATE I got 0.
I have doubts about the rounding of the comparisons.

My setting of the statement are (at the beginning of my proc ) + the standard in SSMS

	SET NUMERIC_ROUNDABORT OFF
	SET ARITHABORT ON
	SET NOCOUNT ON 

Alen

Answer 1

Thanks to all for the answers.

I understood the problem.
I'm rewriting a complex procedure from Sybase in SQL SERVER and in the first there is the possibility of referring to fields created in the statements from the query, while in SQL SERVER I have to do various CTE. The problem was precisely this, in practice the same "dynamic" fields were present in the UPDATE table and therefore I was referring to them without any CTE. After rewriting and reasoning with various CTEs, the values return.

I understood the error by writing an OUTPUT of fields before and after the update. I noticed they entered as NULL, therefore they necessarily had to be the results of the CTEs and not the fields from the table.

	SELECT '#anagrafiche - 3310 pre',  IdAnagrafica, [FatturatoCalc], [NumeroDipendentiCalc],[TotaleAttivoCalc],[IsRetail] FROM #anagrafiche WHERE IdAnagrafica IN (334402) ORDER BY IdAnagrafica;
	CTE 1
	CTE 2
	CTE 3
	UPDATE
	SELECT '#anagrafiche - 3500 after',  IdAnagrafica, [FatturatoCalc], [NumeroDipendentiCalc],[TotaleAttivoCalc],[IsRetail] FROM #anagrafiche WHERE IdAnagrafica IN (334402) ORDER BY IdAnagrafica;
	

Thank you all.

ALEN