Multiple values using case statement

Database
Enthusiast

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

Tags (1)
12 REPLIES
Teradata Employee

Re: Multiple values using case statement

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     

Teradata Employee

Re: Multiple values using case statement

Also, your CREATE TABLE and INSERTs are not correct.

Providing correct/actual scenario/SQLs generally help getting you a solution quickly.

Enthusiast

Re: Multiple values using case statement

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 

Teradata Employee

Re: Multiple values using case statement

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

Enthusiast

Re: Multiple values using case statement

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

Teradata Employee

Re: Multiple values using case statement

And what output do you expect from following:

exec samplemac('0');

Enthusiast

Re: Multiple values using case statement

All the results of 0.
Junior Contributor

Re: Multiple values using case statement

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;
Enthusiast

Re: Multiple values using case statement

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;