Case When Statement in Teradata

Database
Highlighted
Enthusiast

Case When Statement in Teradata

Hi..
I am new to Teradata and really confused with the working of Case statement in Teradata.
Somebody please help me out.. :(

In my select clause,i have a case statement as
CASE
WHEN PROD.PROD_TYPE_CD=INSRNC_TYPE_CD_XREF.PROD_TYPE_CD AND
INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC='NA' AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC AND
FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC AND
FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD= CLM.MBRSHP_SOR_CD AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC='NA' AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND
CLM.MBRSHP_SOR_CD=INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID = BNFT_PKG.BNFT_PKG_ID
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN
INSRNC_TYPE_CD_XREF_LFTOVR. BNFT_PKG_ID= CLM_LINE.BNFT_PKG_ID
AND CLM.MBRSHP_SOR_CD = INSRNC_TYPE_CD_XREF_LFTOVR.MBRSHP_SOR_CD
THEN INSRNC_TYPE_CD_XREF_LFTOVR.VNDR_RQRD_INSRNC_TYPE_CD
END AS INSURANCE_TYPE_CD,

Since the conditions are not mutually exclusive,and i need only one row based on the priority
as like if the first condition in the case statement is satisfied then the other conditions should not be checked and i should get only one row returned,i modified the above statement with Nested CASE as below

CASE
WHEN PROD.PROD_TYPE_CD=INSRNC_TYPE_CD_XREF.PROD_TYPE_CD AND
INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC='NA' AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
ELSE CASE WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
ELSE CASE WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC AND
FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
ELSE CASE WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC AND
FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD= CLM.MBRSHP_SOR_CD AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
ELSE CASE WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC='NA' AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND
CLM.MBRSHP_SOR_CD=INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID = BNFT_PKG.BNFT_PKG_ID
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
ELSE CASE WHEN INSRNC_TYPE_CD_XREF_LFTOVR.PROD_TYPE_CD='NA' AND
INSRNC_TYPE_CD_XREF_LFTOVR.PROD_LVL_4_DESC='NA' AND
INSRNC_TYPE_CD_XREF_LFTOVR.PROD_LVL_3_DESC='NA' AND
CLM.MBRSHP_SOR_CD=INSRNC_TYPE_CD_XREF_LFTOVR.MBRSHP_SOR_CD AND
INSRNC_TYPE_CD_XREF_LFTOVR.BNFT_PKG_ID = BNFT_PKG.BNFT_PKG_ID
THEN
INSRNC_TYPE_CD_XREF_LFTOVR.VNDR_RQRD_INSRNC_TYPE_CD
END END END END
END
END AS INSURANCE_TYPE_CD,

Even then I am getting more than one row since the conditions are not mutually exclusive.
Please let me know,what is wrong with my code and how it should be modified so that i get only one row based on the priority of the conditions.

P.S: I am aware of the rank over and partition by method,but want to do it with the NESTED CASE since i feel,there is nothing wrong with my conditions logically.

Thanks in Advance,
Janaki
14 REPLIES
Junior Apprentice

Re: Case When Statement in Teradata

Hi Janaki,
this is confusing me, you write "In my select clause,i have a case statement " but then "I am getting more than one row".

CASE returns a scalar value, it will never change the number of rows in your result set unless you use it in a Join-condition.
Btw, this is not Teradata problem, CASE works the same in any DBMS.

Dieter
Enthusiast

Re: Case When Statement in Teradata

Hi Dieter,

Thanks for the prompt reply.. :)
My join condition is a lil complicated.
INSRNC_TYPE_CD_XREF is my look up table and my join condition is,

LEFT OUTER JOIN INSRNC_TYPE_CD_XREF INSRNC_TYPE_CD_XREF
ON
(INSRNC_TYPE_CD_XREF. PROD_TYPE_CD = PROD.PROD_TYPE_CD OR INSRNC_TYPE_CD_XREF. PROD_TYPE_CD= 'NA')
AND
(FNCL_PROD_CF.PROD_LVL_3_DESC = INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC OR INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC
='NA')
AND
(FNCL_PROD_CF.PROD_LVL_4_DESC = INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC OR
INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC='NA')
AND
(CLM.MBRSHP_SOR_CD = INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD OR INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA')
AND
( INSRNC_TYPE_CD_XREF. BNFT_PKG_ID= BNFT_PKG.BNFT_PKG_ID OR INSRNC_TYPE_CD_XREF. BNFT_PKG_ID='NA')

AND INSRNC_TYPE_CD_XREF.ST_CD='VT'
AND INSRNC_TYPE_CD_XREF.EXTRCT_TYPE_CD='PHARMACY'


LEFT OUTER JOIN INSRNC_TYPE_CD_XREF INSRNC_TYPE_CD_XREF_LFTOVR
ON
INSRNC_TYPE_CD_XREF_LFTOVR.MBRSHP_SOR_CD=CLM.MBRSHP_SOR_CD AND
INSRNC_TYPE_CD_XREF_LFTOVR. BNFT_PKG_ID=CLM_LINE.BNFT_PKG_ID

AND INSRNC_TYPE_CD_XREF_LFTOVR.ST_CD='VT'
AND INSRNC_TYPE_CD_XREF_LFTOVR.EXTRCT_TYPE_CD='PHARMACY'

It is complicated because the NA values are look up table`s value and these are not present in the source table.

Consider the lay out of my look up table below
PROD_TYPE_CD PROD_LVL_4_DESC PROD_LVL_3_DESC MBR_SOR_CD BNFT_ID RQRD_CD
10 NA NA NA NA 5
NA FFO NA NA NA 6
NA FFO FFO NA NA 7
NA NA NA 123 A3 8

My requirement is
Consider,If for one record the prod_type_cd is 10 and PROD_LVL_4_DESC is FFO,then the case statement which i initially gave will return two RQRD_CD,Since it satisfies both the conditions.
But what i want is if it just sees the PROD_TYPE_CD=10,it should return 5 as the RQRD_CD and should not chk for the next existing condition of PROD_LVL_4_DESC =FFO,such that once it gets the RQRD_CD of 5 it should exit the CASE.Thats why i modified it to a nested case with else condition,but this didn`t work out.

Am i clear now Dieter??

Thanks,
Janaki

Junior Apprentice

Re: Case When Statement in Teradata

HI Janaki,
it's not the CASE, it's the JOIN which creates too many rows.
If you remove the CASE the number of rows shouldn't change, you probably need a ROW_NUMBER to implement that "best fit".

Hopefully you're not running that query against large tables, because the ORed join-conditions will result in a Product Join.

How does the data in your main query's join columns look like?

Dieter
Enthusiast

Re: Case When Statement in Teradata

We have this case to filter out rows obtained using the join based on conditions in priority.
I have tried to imply these conditons in CASE STATEMENT as per priority. but what happens is, when two conditons are satisfied for a single record, we expect it to give one row based on our higest priority case statement, it doesnt prioritize, it gives rows that satisfy all the cases.

OR condition in the join cannot be removed,because if we implement this requirement the other way,it will result in increase in the number of joins.

Precisely,for example if the OR condition gives two rows the CASE should filter out one row based on the Priority.In this case the Sequence of the When Clauses in the CASE statement denotes the priority.

Janaki
Enthusiast

Re: Case When Statement in Teradata

Dieter,
there is no issues with the Joins however.
The problem is i just want to choose the 'best fit' based on the priority (First CASE -> Last CASE).so other CASES are excluded as soon as we fetch a value from any of the CASES with out executing the rest.Thats why i tried implementing the
CASE- WHEN -THEN -ELSE- CASE strategy.

Janaki
Junior Apprentice

Re: Case When Statement in Teradata

Your join returns two rows, because two rows match.
A CASE statement in the SELECT list will never filter any rows.

Logically there's the JOIN* first, followed by WHERE*, GROUP BY, HAVING*, OLAP-function, QUALIFY*, SAMPLE*.
After that the SELECT-list columns are created, finally followed by ORDER BY.
Only those marked with * can filter on rows returned by the previous section.

So you might try to use some CASEs in the JOIN-condition.

Btw, some more joins might still be more efficient than a huge Product Join.

Dieter
N/A

Re: Case When Statement in Teradata

Janu,

You can assign (concatenate) the priority to the conditions in the case statement and you can do a max or min based on the priority and then you can trim off the priority character to get your desired results...

For example:

in the below case statment ..

CASE
WHEN PROD.PROD_TYPE_CD=INSRNC_TYPE_CD_XREF.PROD_TYPE_CD AND
INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC='NA' AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_4_DESC AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_4_DESC AND
FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_3_DESC AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_4_DESC AND
FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_3_DESC AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD= CLM.MBRSHP_SOR_CD AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD

you can do like ..

sel column1 , substr(min( CASE
WHEN PROD.PROD_TYPE_CD=INSRNC_TYPE_CD_XREF.PROD_TYPE_CD AND
INSRNC_TYPE_CD_XREF.PROD_LVL_4_DESC='NA' AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN '1'||INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_4_DESC AND
INSRNC_TYPE_CD_XREF.PROD_LVL_3_DESC='NA' AND INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN '2'||INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_4_DESC AND
FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_3_DESC AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD='NA' AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN '3'||INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
WHEN INSRNC_TYPE_CD_XREF.PROD_TYPE_CD='NA' AND
FNCL_PROD_CF.PROD_LVL_4_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_4_DESC AND
FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.PROD_ LVL_3_DESC AND
INSRNC_TYPE_CD_XREF.MBRSHP_SOR_CD= CLM.MBRSHP_SOR_CD AND
INSRNC_TYPE_CD_XREF.BNFT_PKG_ID='NA'
THEN '4'||INSRNC_TYPE_CD_XREF.VNDR_RQRD_INSRNC_TYPE_CD
end ),2)
from tablesx group by column1

thereby you can assign priority to your desired results..

Note: As per Dieter , it will consume more resource....
Enthusiast

Re: Case When Statement in Teradata

Thanks a lot Dieter and Amrit for your Inputs and Time.. :)

This issue is resolved by implementing separate joins for each condition in the When Clause of the Case statement and choosing the RQRD_CD from the individual joins for them.

Cheers,
Janaki
Enthusiast

Re: Case When Statement in Teradata

Hi Dieter,
I have a small doubt .Request your help -

Supose i have the below query with 1 insert and 7 select statements with UNION ALL clause b/w them.The FROM and the where clause is identical for all the select statements-

Doubt - Is there any alternate way , instead of 7 UNION ALL clause or a 7 insert select as MSR ? i.e. can it be done using some other way than 7 select statemenmts ? Guess we can not use CASE as you mentioned above , it doesnt filter the rows -

E.g. - Given 1st select,others being same except being col names changed e.g. FCF01,FCF02,FCF03...FCF07

INSERT INTO TABLE <>

SELECT
FCC.FEC_PROC AS FEC_PROC
,FCC.COD_PERSONA AS COD_PERSONA
,FCC.IND_MODELO AS IND_MODELO
,'COR' AS IND_MOD_ORIGEN
,FCC.FACTOR_FCF06 AS ID_FACTOR
,CAST(FCC.SCORE_FCF06 AS DECIMAL(21,8)) AS SCORE_FACTOR
,CAST(FCC.FCF06 AS DECIMAL(21,8)) AS VALOR_FACTOR_DEC
,FCC.NUMER_MISSING_FCF06 AS NUMER_MISSING_FCF06
FROM DB1.VAR_CORPORATE_T4 COR
INNER JOIN DB2.FACTOR_CUANT_CORPORATE_T2 FCC
ON COR.FEC_PROC = FCC.FEC_PROC
AND COR.COD_PERSONA = FCC.COD_PERSONA
AND COR.IND_MODELO IN ('COR','NBL')

UNION ALL

SELECT
FCC.FEC_PROC AS FEC_PROC
,FCC.COD_PERSONA AS COD_PERSONA
,FCC.IND_MODELO AS IND_MODELO
,'COR' AS IND_MOD_ORIGEN
,FCC.FACTOR_FCF07 AS ID_FACTOR
,CAST(FCC.SCORE_FCF07 AS DECIMAL(21,8)) AS SCORE_FACTOR
,CAST(FCC.FCF07 AS DECIMAL(21,8)) AS VALOR_FACTOR_DEC
,FCC.NUMER_MISSING_FCF07 AS NUMER_MISSING_FCF07

FROM DB1.VAR_CORPORATE_T4 COR

INNER JOIN DB2.FACTOR_CUANT_CORPORATE_T2 FCC
ON COR.FEC_PROC = FCC.FEC_PROC
AND COR.COD_PERSONA = FCC.COD_PERSONA
AND COR.IND_MODELO IN ('COR','NBL')

UNION ALL



UNION ALL


UNION ALL ...