All Forums Database
20 Feb 2014
Multiple values using case statement

Hello All,
Below is the example:
replace macro samplemac(ratescale varchar(20))
as
(
/*sel colA,colB from table1 f
where f.colA =(case when f.colA in (:ratescale) then :ratescale
                              when :ratescale is null then f.colA
                              else '0' end);*/
 
sel colA,colB from table1 f
where f.colA =(case when f.colA in (:ratescale) then :ratescale
when (:ratescale is not null and f.colA<>:ratescale)  then f.colA
when :ratescale is null then f.colA
else '0' end);

);
/*execute samplemac() -- It showed all the result
execute samplemac(111) -- showed 0 resultds
execute samplemac(2061001) - showed 2061001*/
 
create table table1
(
colA varchar(20);
colA char(20);
)

insert into table1 values(2061001,'Jugal');
insert into table1 values(0,'Anil');
insert into table1 values(2061001,'Jugal');
insert into table1 values(0,'Anil');
exec samplemac('206')-- Expecting all the results including 0 and 2061001
But the output is not coming as expected. For all the scenario's it is giving the Whole output.
Expected output is when i give 2061001 sud show only 2061001 results. When i give blank(Eg. exec samplemac()) sud give all the results including 0 nad 2061001.
When i give something apart from 0 and 2061001, lets say for example i give 206 it sud show all the results of 0 and 2061001

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

In case you execute following:
 
EXECUTE samplemac(2061001) ;
 
You need following rows:
 

2061001 Jugal               

 

 

and NOT following:

 

 

0 Anil                

 

BUT they get qualified due to following condition:

WHEN (:ratescale IS NOT NULL AND f.colA<>:ratescale)  THEN f.colA
 
And your question is confusing .... do you expect following rows with this: exec samplemac('206')
2061001 Jugal     
0 Anil     
 
or only below:
 
2061001 Jugal     
 

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

Also, your CREATE TABLE and INSERTs are not correct.
 
Providing correct/actual scenario/SQLs generally help getting you a solution quickly.

-- If you are stuck at something .... consider it an opportunity to think anew.

20 Feb 2014

Hi Adeel u ve understood it rite..
  When  i execute the below macro with some random number(206), part from 2061001 and 0. I expect all the rows in the output.
exec samplemac('206')
2061001 Jugal
0 Anil 

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

But how do you plan to relate 206 and 0? On what basis?

-- If you are stuck at something .... consider it an opportunity to think anew.

20 Feb 2014

I am relating 206 and 0 on the logic given in the where cause below.
where f.colA =(case when f.colA in (:ratescale) then :ratescale --When 2061001 is true show the result
when (:ratescale is not null and f.colA<>:ratescale) then f.colA -- When 206 show results for 0 and 2061001
when :ratescale is null then f.colA -- when NULL then show results for 0 and 2061001

else '0' end);
I am going wrong somewhere in the case statement. I am not able to figure it out
I am just trying a logic.where the macro behaves on the following conditions:
exec samplemac(); -- show 0 and 2061001 results
exec samplemac('2061001'); -- show only 2061001 results
exec samplemac('206'); --  show 0 and 2061001 results
 

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

And what output do you expect from following:
 
exec samplemac('0');
 

-- If you are stuck at something .... consider it an opportunity to think anew.

20 Feb 2014

All the results of 0.

dnoeth 4628 posts Joined 11/04
20 Feb 2014

OK, I think I know what you want:
If there are any rows matching :ratescale only return those, otherwise return all rows.

SEL colA,colB FROM table1
WHERE colA = :ratescale
OR NOT EXISTS (SELECT * FROM table1 WHERE colA = :ratescale);

or

SELECT colA,colB FROM table1
QUALIFY colA = :ratescale
OR MAX(CASE WHEN colA = :ratescale THEN colA END) OVER () IS NULL;

 

Dieter

21 Feb 2014

Thanks Dieter.. Tat is wat i was looking for.. :)
Dieter.. Im learning fast export. Im not aware on how to apply the .ACCEPT command. Is there a particular format i sud use to insert the values from the param file.
Eg:
.ACCEPT  param1,param2 from param_file;

Raja_KT 1246 posts Joined 07/09
21 Feb 2014

Try this:

.ACCEPT city,zip_code FROM parameter_file;

.begin ....

.export ......

select .....

from abc

 where abc.city_name='&city' and abc.zip_cd='&zip_code'

 

......

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
21 Feb 2014

param_file must be a single line readable text file. Param values are separated by blanks where strings must be enclosed in single quotes, e.g.

 

.ACCEPT numparam, charpara from paran_file;

 

paramfile: 1234  'blabla' 

Dieter

22 Feb 2014

Thanks Dieter n Raja.

You must sign in to leave a comment.