CODEWITHSUNDEEP
sqlteradatadata-warehouse
kannan m
kannan m

Reputation: 67

CASE statement in WHERE clause : Teradata

Below pasted is a sample SQL code which uses a case statement in where clause,but its throwing a syntax error saying "Expecting a Keyword like END between mrktng_pckge_typ_cd and NOT keyword.

CASE WHEN exc_ind=1 THEN mrktng_pckge_typ_cd NOT IN ('a','b','c','d') ELSE NULL END

Upvotes: 1

Views: 9550

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

where      exc_ind <> 1 
        or mrktng_pckge_typ_cd NOT IN ('a','b','c','d')

In case exc_ind can be NULL -

where      coalesce (exc_ind,-1) <> 1 
        or mrktng_pckge_typ_cd NOT IN ('a','b','c','d')

For demonstration purposes:

where case when coalesce (exc_ind,-1) <> 1 or mrktng_pckge_typ_cd NOT IN ('a','b','c','d') then 1 else 0 end = 1

Upvotes: 6

Snehal
Snehal

Reputation: 1088

You might need to do like this. I have given different Table Name but you can do like this logic:

Declare @condition as int = 1
SELECT *
FROM mstCity
WHERE( (1=@condition and Name IN (SELECT AliasCity.Name From mstCity AliasCity WHERE AliasCity.Name NOT IN ('USA','UK') )) OR 
       (2=@condition AND Name IN (SELECT AliasCity.Name From mstCity AliasCity  ))
     )

Updated answer as per your query should be like below:

WHERE ( ( exc_ind= 1 AND mrktng_pckge_typ_cd NOT IN ('a','b','c','d')) OR 
    ( exc_ind <> 1 AND 1=1 )
  )

Upvotes: -1

Related Questions