CASE statement in WHERE clause

Database
Enthusiast

CASE statement in WHERE clause

Hi Gurus,

Is it possbile to use something like below? I am passing $1 and $2 in unix script. 

select * from abc_table a

where

case when $1 > $2  then a.type_id IN (1,2)

else a.type_id IN (3,4)

end

3 REPLIES
Enthusiast

Re: CASE statement in WHERE clause

I think this is not possible. Since both are UNIX variable you can compare the value in UNIX itself and assign another variable with the condition

if [ $1 -gt $2 ] *** assuming it to be number, change as required

then condition = "a.type_cd IN (1,2)"

else

condition="a.type_cd IN(3,4)

fi

Select * from table where $condition;

Cheers,

Mani

Teradata Employee

Re: CASE statement in WHERE clause

WHERE requires a logical expression so CASE is not valid.

But assuming the $1 and $2 are resolved before the statement is passed to BTEQ, if $1 and $2 are expressions that the database must evaluate as part of the query rather than in the script, you could use AND/OR

WHERE

($1 > $2 AND a.TYPE_CD IN (1,2))

OR

($1 <= $2 AND a.TYPE_CD IN (3,4))

Enthusiast

Re: CASE statement in WHERE clause

Hello Folks ,

I just have a requirement with  the dynamic "Where" clause :

Scenario :

There are 2 hardcoded values :

Selection1:

  • yes
  • No

Selection 2:

  • Yes
  • No

Based on selection 1 and selection 2 values (are radio buttons) which is a hardcoded value in a tool , the where clause should vary dynamically.

The selections would be either :

Selection 1 :yes and selection 2: no

Selection 1 :yes and selection 2: yes

Selection 1 :no and selection 2: no

Selection 1 :no and selection 2: yes

Required :

This scenario for this "Dynamic Where Clause" should be handled at the query level.

The where clause should be some thing like

Where

CASE WHEN '?value'='Yes' AND '?value1'='No'

THEN

tablename.column1 in (value1,value2,value3)

and tablename.column2 like any (value,valu4,value5)

and.......

ELSE

WHEN  '?value'='Yes' AND '?value1'='Yes'

THEN

tablename.column1 in (value11,value25,value09)

and tablename.column2 like any (value6,valu84,value95)

and.......

ELSE

WHEN  '?value'='No' AND '?value1'='Yes'

tablename.column1 in (value11,value25,value09)

and tablename.column2 like any (value6,valu84,value95)

and.......

ELSE

WHEN  '?value'='No' AND '?value1'='No'

THEN

tablename.column1 in (value11,value25,value09)

and tablename. column2 like any (value6,valu84,value95)

and.......

END

Explanation :

So in the above code '?value' and '?value1' values in teradata would be passed at runtime and condition would be checked and confirmed based on which any one of the "Dynamic Where clause" should be called.

Queries :

  • I just would want to know if this could be achieved .
  • Do we have to go for nesting of Case statements?
  • Can any one please help me with a syntax for building this dynamic where clause.

Any help is greatly appreciated , Thanks in Advance.

Regards ,

Shavyani!