All Forums Database
rediff123 14 posts Joined 02/09
12 Oct 2009
COALESCE

I need to write a query, so that it returns the non-null value like the following,COALESCE (db_1.appl_no, db_2.appl_no) as Appl_nbrBut, db_1.appl_no has both '?' and ' ' here the coalesce function takes '?' as Null, but returns the blank field ' ' assuming it to be Not Null.so i change the query like the following,COALESCE (CASE WHEN db_1.appl_no = ' 'THEN NULLELSE db_1.appl_no END , db_2.applno as appl.no) as Appl_nbrIs there any other better way???Pls help-Thank you

Jimm 298 posts Joined 09/07
13 Oct 2009

CASE WHEN db_1.appl_no = ' 'THEN db_2.appl_noELSE db_1.appl_no END as Appl_nbrLooks neater!

rediff123 14 posts Joined 02/09
13 Oct 2009

Jimm,thanks for the reply.here the problem is i need to pick the not null value between ---> COALESCE (db_1.appl_no, db_2.appl_no)but the db_1.appl_no itself has three possibilites ---> Null value (?), Blank value (' ') & also a Variable number in it('211234423').the coalesce considers the blank field (' ') in db_1.appl_no as not null and picks it , instead of picking the number value in the db_2.appl_noso is there any other way instead of using coalesce (case when ' ' then .. end, ........)

Jimm 298 posts Joined 09/07
13 Oct 2009

NoCASE db_1.appl_no WHEN ' 'THEN db_2.appl_noWHEN NULLTHEN db_2.appl_noELSE db_1.appl_no END as Appl_nbrIf you look at the explain of a COALESCE, it is simply a short form of CASE.

RB185048 32 posts Joined 09/08
21 Oct 2009

Hi ,I think you can try below. ( COALESCE(NULLIF ( db_1.appl_no,'') ,db_2.appl_no) regards,rupesh

You must sign in to leave a comment.