Passing Values to IN statement using Case

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Passing Values to IN statement using Case

REPLACE MACRO macro1 (typeOfFetch VARCHAR(10) ,LVL INT) AS
(
SEL lvl,b,c
FROM Hirarchy
WHERE lvl IN (CASE WHEN :LVL = 0 THEN TRIM( '('||'1'||','||'2'||','||'3'||','||'4'||','||'5'||','||'6'||','||'7'||','||'8'||')') ELSE :LVL END)
;
);

 

EXEC FetchHirarchy('a',0);

 

I am trying to fetch all levels when parameter LVL is 0, This is not working .. any help?

 


Accepted Solutions
Junior Contributor

Re: Passing Values to IN statement using Case

You can't pass a dynamic string to an IN (without using Dynamic SQL in a Stored Procedure), but can replace your CASE with an ORed condition:

WHERE (lvl = :LVL OR :LVL = 0)
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Passing Values to IN statement using Case

You can't pass a dynamic string to an IN (without using Dynamic SQL in a Stored Procedure), but can replace your CASE with an ORed condition:

WHERE (lvl = :LVL OR :LVL = 0)
Enthusiast

Re: Passing Values to IN statement using Case

Thankyou Dieter .. exactly what i needed. :)

Tags (1)