All Forums Database
11 Feb 2014
Check Case Statement NULL

Hi Guys,
In the case statement if i give it as mentioned below:
where col1=(case when :col1='xyz' then 'xyz' else NULL end)
Here if the case statement is satisfied then col1='xyz' acordingly the results are shown. Else col1=NULL. Which is not logical. The expectation is when i pass a NULL parameters(' '), then the case statement is not satisfied and the result has to be the combination of both NULL and 'xyz'
Is there any alternative to pass the NULL value to  the col1. I tried using NULLIF and COALESCE but the NULL values are not getting evaluated.
 

dnoeth 4628 posts Joined 11/04
11 Feb 2014

where col1 = :col or :col is null?
Could you please tell exactly what you're trying do do?

Dieter

M.Saeed Khurram 544 posts Joined 09/12
11 Feb 2014

Hi,

where col1=(case when :col1='xyz' OR :col1 is null then 'xyz' else NULL end)

 

Khurram

11 Feb 2014

Dnoeth/Saeed,
where col1=(case when :col1='xyz' then 'xyz' else NULL end)
when i pass a parameter xyz then the where condition satisfies and all the results displayed which are equal to xyz. If the condition does not satisfies(Else part) then all the results matching xyz and NULL has to be displayed.
 
Dnoeth,
I hope im helping u understand what i expect.
 

dnoeth 4628 posts Joined 11/04
11 Feb 2014

Is this 'xyz' hard-coded?
where col1 ='xyz' or (:col1 is null and col1 is null) 

Dieter

11 Feb 2014

Yes. I had hard-coded xyz. Still im not getting the result for both xyz and NULL values. When i pass the parameter as ' '(single quoates)

dnoeth 4628 posts Joined 11/04
11 Feb 2014

Of course, NULL and '' are totally different.
where col1 ='xyz' or ((:col1 is null or :col1 = '') and col1 is null) 

Dieter

11 Feb 2014

When i give it as
where NULLIF(col1,:col1) =:col1 or (:col1 in ('xyz',NULL)).
Does this make sense. When i pass the parameter as ' '. Then NULLIF is satisfied along with the or clause. Else if i pass 'xyz' then only the column satisfying 'xyz' is displayed

11 Feb 2014

Hi Dnoeth,
 
I applied the logic which u suggested with some  changes:
Where COALESCE(col1,'')=COALESCE(NULLIFZERO(:col1),'',NULL) or (col1(NULL,'xyz));
 
Thanks Dnoeth. Ur suggestions. And everytime u correct me wen i go wrong is really turning out be to a very gud learning curve. Thanks again.. :)

14 Feb 2014

In the script when i give the below where clause
Where COALESCE(colA,'')=COALESCE(NULLIFZERO(:col1)
                                     ,NULLIFZERO(:col2)) or (col1 in(NULL,'xyz));
when i pass the parameter to the macro in different scenarios:
(i) exec sam('')-- It shows all the values including NULL and XYZ
(ii) exec sam('xyz')-- It shows  only values having XYZ
(iii) exec sam('A')-- It shows only values having XYZ. Which i do not want in my output. I expect the output as NULL.
Please me some logic to get NULL value. When there is no matching result in the table.

14 Feb 2014

When i mean it as NULL. I expect 0 results

You must sign in to leave a comment.