If the value of variant isn't Null, then the Nz function returns the value of variant.
Can i return a custom value if the variant isnt null? are there any other commands that might suit this purpose?
The correct syntax is
COALESCE (value1, value2, value3, ...)
Meaning: return vaulue1 unless it IS NULL, then take value2 unless it IS NULL, then take value3 unless....
In other words, return the first NOT NULL value. When all values are NULL, return NULL.
All the data types must be compatible, so you can not write
SELECT COALESCE(wage, 'no wage specified') as wage -- one can not have both numerical and alphanumerical values in one column
SELECT COALESCE(wage, 0.0) as wage -- all numerical values
SELECT COALESCE(CAST(wage as VARCHAR), 'no wage specified') as wage -- all alphanumerical values
Can i return a custom value if the variant isnt null?
No, when the variant isn't NULL, the value of the variant will be returned, by definition.
Last edited by Wim; 02-18-12 at 18:42.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages