Null on one but not the other 3

[markajem]

Sorry about the name of the title but hard to describe my problem.

I am trying to capture NULLs on both ends. Meaning if sopnumbe is null then use order_num and if order_num is null then use sopnumbe AS master_SOP_Num.

This works ok if order_num is null it populates master_SOP_Num, but if sopnumbe is null it does not even pick up that record.

I tried method #1 and also #2 and they both give me the same results


#1

select	case
	when sopnumbe is null
	then order_num
	else sopnumbe

#2

select Coalesce(order_num,sopnumbe ) As master_SOP_Num,

Present results:
[tt]
master_sop_num sopnumbe order_num
342234 342234 342234
342235 342235 <null>
342237 342237 <null>
[/tt]

Desired results:
[tt]
master_sop_num sopnumbe order_num
342234 342234 342234
342235 342235 <null>
342236 <null> 342236
342237 342237 <null>
[/tt]

As you can see 342236 is missed altogether in my present code.

Thanks for your help

 

[vongrunt]

When rows are missing then either WHERE clause or joins do not work as expected... post complete query here.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[kunai]

Instead of CASE, try the isNull function.

In your case, i'd be:

select isNull(sopnumbe, order_num) as master_sop_num from yourTable

 

[r937]

your COALESCE works just fine, i tested it

(aside: use COALESCE instead of ISNULL)

see vongrunt's suggestion -- there's probably something else going on

r937.com | rudy.ca

 

[markajem]

Thnx for all your help.

It was the 'where' clause. I was selecting only certain records from the file that contained field "sopnumbe" and of course that particular record did not exist because of the <null> so therefore did not pull in the VIEW.

Thanks again.