SQL nvl equivalent - without if/case statements & isnull & coalesce

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

From stackoverflow

• ISNULL (for a single replace)

  or

  COALESCE (Returns the first nonnull expression among its arguments.)

  CheeseConQueso : isnull & coalesce are both undefined routines in whatever version im using....

  BradC : What version are you using?

• 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.

  CheeseConQueso : Yea, 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

  CheeseConQueso : ....but it works in other situations thanks

• 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

• 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.