SQL nvl equivalent - without if/case statements & isnull & coalesce
Solution 1
You seem to be using Informix.
AFAIK, there is DECODE there:
DECODE(field, NULL, 'it is null, man', field)
should give you same result as NVL(field, 'it is null, man')
Please post exact name and version of the RDBMS you are using.
Solution 2
ISNULL (for a single replace)
or
COALESCE (Returns the first nonnull expression among its arguments.)
Solution 3
SQL Server: IsNull or COALESCE http://msdn.microsoft.com/en-us/library/ms184325.aspx
Sybase: isnull function http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks162.htm
Postgres: I couldn't find one though haven't fully checked. Suggests to select where IS NULL and build from here http://archives.postgresql.org/pgsql-sql/1998-06/msg00142.php
DB2 - COALESCE http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000780.htm
Solution 4
The problem with your DECODE statement that is generating the 800 error is simple. '01/01/2009'
is being treated as a string, and it's actually the 4th argument that generates the error.
Appreciate that the input and output of a DECODE statement can be different data-types, so the engine requires you to be more explicit in this case. (Do you want purge_date
cast as a string or the string '01/01/2009'
, or the string argument parsed as a date or the original date? There's no way for the engine to know.
Try this:
SELECT DECODE(purge_date, NULL, '01/01/2009'::DATE, purge_date)
You could also write that 3rd argument as:
DATE('01/01/2009')
MDY(1,1,2009)
depending on version and personal preference.
CheeseConQueso
facebook.com/CheeseConQueso - Facebook pocketband.net - uLoops/PocketBand grooveshark.com/CheeseConQueso - Grooveshark
Updated on January 19, 2020Comments
-
CheeseConQueso over 4 years
Are there any nvl() equivalent functions in SQL?
Or something close enough to be used in the same way in certain scenarios?
UPDATE:
no if statements
no case statements
no isnull
no coalesce
select nvl (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581; (expression) SODIUFOSDIUFSDOIFUDSF 1 row(s) retrieved. select isnull (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581; 674: Routine (isnull) can not be resolved. Error in line 1 Near character position 8 select coalesce (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581; 674: Routine (coalesce) can not be resolved. Error in line 1 Near character position 8 select decode(purge_date, NULL, "01/01/2009", purge_date) from id_rec where id=74115; 800: Corresponding types must be compatible in CASE expression. Error in line 1 Near character position 57
-
topski over 15 yearsExact version you are using would be helpful. Also, if nvl works, why not use it?
-
CheeseConQueso over 15 yearsI'm not sure how to find out which version im using.. its informix and its old... for one scenario nvl issn't working and i don't know why
-
topski over 15 yearsWhat's the scenario that NVL is causing a problem?
-
CheeseConQueso over 15 yearsThere is a field in one of my tables that is 'null' ("" or " ") and its supposed to be the name of someone. We set one key up for general usage and never put a name in. It finally hit the fan and I had to fix it, but NVL didnt do it, I had to get around it with a bunch of BS
-
Jonathan Leffler over 15 yearsDon't forget that unlike some other systems, Informix does not treat an empty string as NULL - the two are different. Running an Informix program with the '-V' option should print some version information. The name of the program you chose might help, too.
-
Jonathan Leffler over 15 yearsConsequently, what you probably need is a condition WHERE somecolumn = ' ' or an equivalent (any number of spaces, including zero, is OK; you can use double quotes instead of single quotes, too).
-
-
CheeseConQueso over 15 yearsYea, old informix.. dont know what version... i got this error from that syntax select decode(purge_date, NULL, "01/01/2009", purge_date) from id_rec where id=74115; 800: Corresponding types must be compatible in CASE expression. Error in line 1 Near character position 57
-
Kenny Drobnack about 12 yearsI'm working on Sybase 12.5. Neither DECODE nor NVL are available but ISNULL worked great.
-
TWiStErRob about 11 years
DECODE
is working nice, but the date you're trying to give is astring
, or with implicit conversion I guess it'sdatetime year to second
or whatever the default is. So useDECODE(purge_date, NULL, EXTEND('01/01/2009', year to day), purge_date) from id_rec
to convert topurge_date
's format. (Just noticed, the same explanation is down below in @RET's answer)