Subscribe to this blog
Briefcase
Print
NullIf can be a very handy function sometimes. For those who have not used it, if the result of the two parameters are equal then null is returned. So if we execute :
- Select nullif(10,10) as Result1
- Select nullif(10,9) as Result2
We get the result of
For some test data, I required a column that was randomly null. Using ABS(CHECKSUM(NEWID())) to generate the random value and then modulus 10, to get a random value between 0 and 9 , then passing that into NULLIF as one parameter and 10 as the other, I should of got random values between 1 and 9 with a selection of NULL values. Here’s the code :
- select top(1000) ROW_NUMBER() over (order by (select null)) as ID,
- nullif(ABS(checksum(newid()))%10,0) as RandomNonZero
- from sys.columns a
And here's the result…
Interesting , does that mean that NULLIF is not working ? Plainly it is sometimes, after all there are some NULL values.
Much like in my previous post detailing the differences between ISNULL and COALESCE, the NULLIF function is expanded by the engine into a case statement as shown below.
So, its working but the value outputted is not the value tested.
Posted by Joe Celko on 14 August 2010
The definition right out of the ANSI/ISO Standares is:
NULLIF(a, b) ::= (CASE WHEN (a = b) THEN NULL ELSE a END);
I never considered math rounding and conversion errors:
NULLIF (x, 0) versus NULLIF (x, 0.0) versus NULLIF (x, 0.0e0).
Maybe using CAST (<exp> AS INTEGER) for the first parameter is a better idea.
Posted by Andrew Morton-340351 on 14 August 2010
It doesn't work because newid() generates a new id every time it is called, so the first newid() in the case statement is not the same newid() in the else part. The workaround is to set a variable to ABS(checksum(newid())) and use that in the nullif function.