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

Up vote 1 down vote favorite 2 share g+ share fb share tw.

UPDATE: no if statementsno case statementsno isnullno 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 sql syntax informix null link|improve this question edited Jan 16 '09 at 19:37 asked Jan 16 '09 at 16:37CheeseConQueso1,77032360 91% accept rate.

Exact version you are using would be helpful. Also, if nvl works, why not use it? – BradC Jan 16 '09 at 18:08 I'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 – CheeseConQueso Jan 16 '09 at 19:27 What's the scenario that NVL is causing a problem?

– BradC Jan 16 '09 at 19:40 There 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 didn't do it, I had to get around it with a bunch of BS – CheeseConQueso Jan 16 '09 at 21:01 1 Don'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 Jan 16 '097 at 3:53.

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.

Yea, old informix.. don't 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 Jan 16 '09 at 19:31 ....but it works in other situations thanks – CheeseConQueso Jan 16 '09 at 21:15.

ISNULL (for a single replace) or COALESCE (Returns the first nonnull expression among its arguments. ).

SQL Server: IsNull or COALESCE msdn.microsoft.com/en-us/library/ms18432... Sybase: isnull function infocenter.sybase.com/help/index.jsp?top... Postgres: I couldn't find one though haven't fully checked. Suggests to select where IS NULL and build from here archives.postgresql.org/pgsql-sql/1998-0... 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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.