Conditional where clauses

UDA

Conditional where clauses

Hi,

I want to implement an IF-ELSE logic in my where clause, some thing like below. I want the conditions in the IF-ELSE clause to be executed in sequence. I tried case statement also, but it doesnt work. Is there any way to implement such logic in Teradata? I am new to Teradata, hence any of your inputs would help me a lot.

FROM
F_SR_DMD_SALES_T DMD LEFT OUTER JOIN
LKP_AMGEN_PRD_TO_PI_SLS_SPLIT LKP
ON
DMD.MATERIAL_KEY = LKP.AMGEN_MATERIAL_KEY
AND DMD.ICO_FIN_DAY_KEY_FIRST_DAY = LKP.ICO_FIN_DAY_KEY
AND ( IF LKP.MEDICAL_CENTER_KEY NOT IN (-1, -2)
THEN DMD.MEDICAL_CENTER_KEY = LKP.MEDICAL_CENTER_KEY AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY
ELSE IF LKP.BRICK_MICROBRICK_KEY NOT IN (-1, -2)
THEN DMD.BRICK_MICROBRICK_KEY = LKP.BRICK_MICROBRICK_KEY AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY
ELSE IF LKP.TERR_CLASSIFICATION_KEY NOT IN ( -1, -2)
THEN DMD.TERR_CLASSIFICATION_KEY = LKP.TERR_CLASSIFICATION_KEY AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY
ELSE IF LKP.MEDICAL_CENTER_KEY IN (-1, -2) AND LKP.BRICK_MICROBRICK_KEY IN (-1, -2) AND LKP.TERR_CLASSIFICATION_KEY IN ( -1, -2)
THEN DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY
END IF)
AND LKP.LOGICAL_DEL_FLAG = 'N'
1 REPLY
Enthusiast

Re: Conditional where clauses

Try this below (but check it gives you the result you expect):

FROM F_SR_DMD_SALES_T DMD
LEFT OUTER JOIN LKP_AMGEN_PRD_TO_PI_SLS_SPLIT LKP
ON DMD.MATERIAL_KEY = LKP.AMGEN_MATERIAL_KEY
AND DMD.ICO_FIN_DAY_KEY_FIRST_DAY = LKP.ICO_FIN_DAY_KEY
AND ((LKP.MEDICAL_CENTER_KEY NOT IN (-1, -2)
AND DMD.MEDICAL_CENTER_KEY = LKP.MEDICAL_CENTER_KEY
AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY)
OR (LKP.BRICK_MICROBRICK_KEY NOT IN (-1, -2)
AND DMD.BRICK_MICROBRICK_KEY = LKP.BRICK_MICROBRICK_KEY
AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY)
OR (LKP.TERR_CLASSIFICATION_KEY NOT IN ( -1, -2)
AND DMD.TERR_CLASSIFICATION_KEY = LKP.TERR_CLASSIFICATION_KEY
AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY)
OR (LKP.MEDICAL_CENTER_KEY IN (-1, -2)
AND LKP.BRICK_MICROBRICK_KEY IN (-1, -2)
AND LKP.TERR_CLASSIFICATION_KEY IN ( -1, -2)
AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY))
AND LKP.LOGICAL_DEL_FLAG = 'N'

The exectution sequence does not matter in a join like this. The rows will only be included once, whuchever condition is satisfied.
If you really want to use a CASE, then:

FROM F_SR_DMD_SALES_T DMD
LEFT OUTER JOIN LKP_AMGEN_PRD_TO_PI_SLS_SPLIT LKP
ON DMD.MATERIAL_KEY = LKP.AMGEN_MATERIAL_KEY
AND DMD.ICO_FIN_DAY_KEY_FIRST_DAY = LKP.ICO_FIN_DAY_KEY
AND (CASE WHEN LKP.MEDICAL_CENTER_KEY NOT IN (-1, -2)
AND DMD.MEDICAL_CENTER_KEY = LKP.MEDICAL_CENTER_KEY
AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY
THEN 1
WHEN LKP.BRICK_MICROBRICK_KEY NOT IN (-1, -2)
AND DMD.BRICK_MICROBRICK_KEY = LKP.BRICK_MICROBRICK_KEY
AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY
THEN 1
WHEN LKP.TERR_CLASSIFICATION_KEY NOT IN ( -1, -2)
AND DMD.TERR_CLASSIFICATION_KEY = LKP.TERR_CLASSIFICATION_KEY
AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY
THEN 1
WHEN LKP.MEDICAL_CENTER_KEY IN (-1, -2)
AND LKP.BRICK_MICROBRICK_KEY IN (-1, -2)
AND LKP.TERR_CLASSIFICATION_KEY IN ( -1, -2)
AND DMD.SUB_AFFILIATE_KEY = LKP.SUB_AFFILIATE_KEY
THEN 1 ELSE 0 END) = 1)
AND LKP.LOGICAL_DEL_FLAG = 'N'

Should give the same results as above, and same performance.