All Forums Database
sps1 9 posts Joined 01/15
04 May 2015
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

manib0907 61 posts Joined 04/15
04 May 2015

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

Cheers,
Mani

Fred 1096 posts Joined 08/04
06 May 2015

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

shavyani 23 posts Joined 03/15
28 Oct 2015

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!

You must sign in to leave a comment.