Check Case Statement NULL

Database
Enthusiast

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.

Tags (2)
10 REPLIES
Junior Contributor

Re: Check Case Statement NULL

where col1 = :col or :col is null?

Could you please tell exactly what you're trying do do?

Enthusiast

Re: Check Case Statement NULL

Hi,

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

Re: Check Case Statement NULL

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.

Junior Contributor

Re: Check Case Statement NULL

Is this 'xyz' hard-coded?

where col1 ='xyz' or (:col1 is null and col1 is null) 

Enthusiast

Re: Check Case Statement NULL

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)

Junior Contributor

Re: Check Case Statement NULL

Of course, NULL and '' are totally different.

where col1 ='xyz' or ((:col1 is null or :col1 = '') and col1 is null) 

Enthusiast

Re: Check Case Statement NULL

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

Enthusiast

Re: Check Case Statement NULL

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.. :)

Enthusiast

Re: Check Case Statement NULL

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.