All Forums Database
Janu 5 posts Joined 11/10
08 Nov 2010
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_SO R_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

dnoeth 3240 posts Joined 11/04
08 Nov 2010

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

Dieter

Janu 5 posts Joined 11/10
08 Nov 2010

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

dnoeth 3240 posts Joined 11/04
08 Nov 2010

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

Dieter

Janu 5 posts Joined 11/10
08 Nov 2010

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

Janu 5 posts Joined 11/10
08 Nov 2010

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

dnoeth 3240 posts Joined 11/04
08 Nov 2010

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

Dieter

Amirtd 1 post Joined 11/10
09 Nov 2010

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

Janu 5 posts Joined 11/10
09 Nov 2010

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

shokap 9 posts Joined 02/11
26 Apr 2011

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

dnoeth 3240 posts Joined 11/04
27 Apr 2011

If this is used to normalize a repeating group you can replace the UNIONs with a single CROSS JOIN to a table with 7 rows in it and then use a CASE like this:

SELECT
FCC.FEC_PROC AS FEC_PROC
,FCC.COD_PERSONA AS COD_PERSONA
,FCC.IND_MODELO AS IND_MODELO
,'COR' AS IND_MOD_ORIGEN
,case dt.i
when 1 then FCC.FACTOR_FCF01
when 2 then FCC.FACTOR_FCF02
when 3 then FCC.FACTOR_FCF03
...
end AS ID_FACTOR
,case dt.i
when 1 then CAST(FCC.SCORE_FCF06 AS DECIMAL(21,8))
when 2 then ...
end AS SCORE_FACTOR
,...
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')
CROSS JOIN
(select day_of_calendar as i from sys_calendar.calendar
where i between1 and 7) as dt

Dieter

Dieter

srividya.912 3 posts Joined 07/11
08 Jul 2011

Thanks Dieter..ur explanations seem to be very useful..

vinothkumar84 2 posts Joined 05/12
03 May 2012

hi janaki,

Could you post the query you have used to resolve the issue.

I wish to know how we can use joins in WHEN clause .

I'm getting a syntax error while doing so.

Pls help

ismaildreams 1 post Joined 09/12
13 Sep 2012

SELECT NET_MUMD_AMT,
(CASE
when MKUP_MKDN_RSN_CD=26
THEN NET_MUMD_AMT AS BUYER_MKDN_PRICE_CHANGE_AMT
END),
(CASE
WHEN MKUP_MKDN_RSN_CD=25
THEN NET_MUMD_AMT AS BUYER_MKDN_PRICE_CHANGE_AMT
END),
(CASE
WHEN MKUP_MKDN_RSN_CD=27
THEN NET_MUMD_AMT AS BUYER_MKDN_CLEARANCE_AMT
END),
(CASE
WHEN MKUP_MKDN_RSN_CD=9
THEN NET_MUMD_AMT AS BUYER_MKDN_HD_COUPONS_AMT
END),
(CASE
WHEN MKUP_MKDN_TYP_CD=8
THEN  NET_MUMD_AMT AS TOTAL_BUYER_MKDN_AMT END),
(CASE
WHEN MKUP_MKDN_TYP_CD =4
THEN NET_MUMD_AMT AS TOTAL_MKUP_AMT END )
FROM
QA_US_FINANCE_VIEWS.STR_ITEM_A_MUMD_W
INNER JOIN
select DISTINCT
A.ITEM_SKU_NBR,B.LOC_NBR AS STORE_NBR,D.FSCL_PRD_KEY_VAL as fscl_wk_nbr,
C.NET_MUMD_AMT,C.MKUP_MKDN_RSN_CD from
QA_US_FINANCE_VIEWS.STR_ITEM_A_MUMD_W C
LEFT OUTER JOIN
QA_SHRD_VIEWS.LOC_HIER B
ON
C.STR_LOC_ID=B.LOC_ID
LEFT OUTER JOIN
QA_SHRD_VIEWS.ITEM_HIER A
ON
C.ITEM_ID=A.ITEM_ID
LEFT OUTER JOIN
QA_SHRD_VIEWS.FSCL_WK_HIER D
ON
C.FSCL_WK_END_DT=D.FSCL_WK_END_DT
sample 5
 

 

one column mapped to different column from source to target how can i test is it right else pls tell me solution

 

13 Sep 2012

Select the column on which the case statement is checked alongwith the other column to see which of the case statement executed and verify the output.

You must sign in to leave a comment.