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