Select case help 1

[Glowworm27]

Hello all,

I am joining some tables together to produce a report.
In one of the joins I am doing a left join and occasionaly that will not return data to my select query.

I am trying to create a select case query that when the left join does not join a record to use the data in another field but I cant get it to work

select 
Case rtrim(nm.last)  WHEN NULL 
   Then  (rtrim(cd.partylastname) || ', ' || rtrim(cd.PartyFirstName))
   ELSE  (rtrim(nm.last) || ', ' || rtrim(nm.First) || ' ' || rtrim(nm.middle)) END as personName


From temp_co_data cd
left join nmmain nm on cd.Name_ID = nm.Name_ID

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[jaxtell]

I don't think you can compare to null. This returns 2

select case null when null then 1 else 2 end from dual

You'll need to use an nvl

select case nvl(null,'ZZZ') when 'ZZZ' then 1 else 2 end from dual

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Glowworm27]

Jaxtell,

Thanks that did the trick. I was pretty sure you couldnt compare nulls either but for the life of me could not remember why, or how to get around it.

Thanks again
G

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[SantaMufasa]

I don't think you can compare to null.

Actually, you can compare to NULL directly, but you must use the "is null" comparison:

select case when null is null then 1 else 2 end result from dual;

    RESULT
----------
         1

If you compare using the "=" operator, then the answer (although not FALSE), is never TRUE, therefore you receive the NOT TRUE result:

select case when null = null then 1 else 2 end result from dual;

    RESULT
----------
         2

Let us know if this is reasonable to you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”