Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic to the Top
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##
##### Case When Statement in Teradata

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-08-2010
04:17 AM

11-08-2010
04:17 AM

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

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

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

FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.P

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

FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.P

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_C

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_C

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

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

FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.P

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

FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.P

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_C

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

INSRNC_TYPE_CD_XREF_LFTOVR.BNFT_PKG_ID = BNFT_PKG.BNFT_PKG_ID

THEN

INSRNC_TYPE_CD_XREF_LFTOVR.VNDR_RQRD_INSRNC_TYPE_C

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-08-2010
05:42 AM

11-08-2010
05:42 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-08-2010
06:18 AM

11-08-2010
06:18 AM

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

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

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='PHARMAC

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-08-2010
07:00 AM

11-08-2010
07:00 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-08-2010
07:16 AM

11-08-2010
07:16 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-08-2010
07:39 AM

11-08-2010
07:39 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-08-2010
08:17 AM

11-08-2010
08:17 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-09-2010
02:37 AM

11-09-2010
02:37 AM

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

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

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

FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.P

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

FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.P

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

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

FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.P

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

FNCL_PROD_CF.PROD_LVL_3_DESC=INSRNC_TYPE_CD_XREF.P

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-09-2010
09:45 PM

11-09-2010
09:45 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-26-2011
12:17 AM

04-26-2011
12:17 AM

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.