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 for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

06-23-2011
12:30 PM

06-23-2011
12:30 PM

Hi!

I hve this insert qry which runs for a long time.This is cmplex qry but I thnk we can remove some unwantes case stmts.

Please suggest how to tune it or remove the unnecesaary prts.

eg:

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'XYZ' or

can be changed to:

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS in ('DEF') then 'XYZ'

else ‘Uns’ end = 'XYZ' or

INSERT INTO IND ( GID, TGT_LVL_ID )

select distinct '478b6a70-0be7-1000-8029-0a416d330000' as c1, D1.c1 as c2

from

(select A.CNTCT as c1

from PRSN_D_CUST A

where ( A.CNTCT in

(select distinct D1.c1 as c1 from

((((((((((((

(

(

(select distinct B.QLFD_ID as c1

from M_C_IND B

where ( B.GID = '123' )

intersect

select A.CNTCT as c1

from PRSN_D_CUST A

where ( A.PIN between '1' and '999999999' )

)

intersect

select distinct C.CNTCT as c1

from SUBPLAN_MNTHLY_CUST C

where ( 0 < (coalesce(C.BALANCE , 0)) )

)

except

select A.CNTCT as c1

from PRSN_D_CUST A

where ( (coalesce(( CAST((( TIMESTAMP '2011-04-01 00:00:00' - A.BIRTH_DT ) YEAR(4)) AS INTEGER) ) , 0)) < 18 )

)

except

select distinct A.CNTCT as c1

from

PRSN_D_CUST A,

PRSN_DX D /* Al_PRSN_DX (PRSN_DX) */ ,

POSITION_D E /* Dim_POSITION_D_WMA */ ,

POSITION_DX F /* Dim_POSITION_DWMA */

where (

A.PR_POSTN = E.POSITION_NAME and F.ROW = E.ROW

and A.CNTCT = D.ROW

and (

case when D.ENGAGED = 'Y' then 'Y' else 'N' end = 'Y' or

case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'

when F.ASGN_STS in ('LMN', 'OPQ', 'PQR', 'UST', 'STU', 'VWX') then 'N' else 'U' end = 'Y' or

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'XYZ' or

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'ABC'

or D.FNX2_ATRB_03 is not null

) ) )

except

(select A.CNTCT as c1

from

PRSN_D_CUST A

where ( case when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW' when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'A. HN'

or

case

when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'B. A I'

or

case

when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'I. UNC'

or

case

when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'J. Unknwn'

or case

when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'K TO' )

union

(select A.CNTCT as c1

from PRSN_D_CUST A

where (

case when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn' end = 'C. A II' )

intersect

select distinct X.CNTCT as c1

from PRSN_F_CUST X

where ( X.AS_OF_DT = 20110101

and 375000 < X.RA_GRA_ACM_AMT + X.SRA_GSRA_ACM_AMT + X.PAN_ACM_AMT + X.TSP_ACM_AMT + X.IRA_ACM_AMT + X.IAN_ACM_AMT + X.MFS_ACM_AMT + X.CSA_ACM_AMT ) )))

except

(select A.CNTCT as c1

from PRSN_D_CUST A

where ( (

case when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn' end = 'G. E A' or

case when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn' end = 'H. Mass Market'

)

and (A.CUSTOMER_STS = 'PU-IA' or A.CUSTOMER_STS = 'PU-IRA' or A.CUSTOMER_STS = 'PU-ONLY' or A.CUSTOMER_STS = 'PU-RETAIL') )

intersect

select distinct X.CNTCT as c1

from PRSN_F_CUST X

where ( X.AS_OF_DT = 20110401 and X.RA_GRA_ACM_AMT + X.SRA_GSRA_ACM_AMT + X.PAN_ACM_AMT + X.TSP_ACM_AMT + X.IRA_ACM_AMT + X.IAN_ACM_AMT + X.MFS_ACM_AMT + X.CSA_ACM_AMT < 10000 ) ))

except

select distinct A.CNTCT as c1

from PRSN_D_CUST A,

CLUSTER_D_CUST Y

where ( A.CLUSTER = Y.ROW

and (A.CUSTOMER_STS = 'IA-ONLY'

or A.CUSTOMER_STS = 'OTHER'

or A.CUSTOMER_STS = 'RETAIL'

or A.CUSTOMER_STS = 'TUITION-ONLY'

or

case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG' when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'

when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'

when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'

when Y.CST_ST_CD = 'e:AN' then '5. Ann'

when Y.CST_ST_CD = 'g:OT' then '6. Other'

else '7. Unknwn' end = '4. Non-Rtrmnt Services'

or

case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG' when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'

when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'

when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'

when Y.CST_ST_CD = 'e:AN' then '5. Ann'

when Y.CST_ST_CD = 'g:OT' then '6. Other'

else '7. Unknwn' end = '5. Ann'

or case

when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG'

when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'

when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'

when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'

when Y.CST_ST_CD = 'e:AN' then '5. Ann'

when Y.CST_ST_CD = 'g:OT' then '6. Other'

else '7. Unknwn' end = '6. Other'

or case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG'

when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'

when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'

when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'

when Y.CST_ST_CD = 'e:AN' then '5. Ann'

when Y.CST_ST_CD = 'g:OT' then '6. Other'

else '7. Unknwn' end = '7. Unknwn') ) )

except

select Z.TGT_LVL_ID as c1 from IND Z

where ( Z.GID = 'ABC' ) )

except

select Z.TGT_LVL_ID as c1 from IND Z

where ( Z.GID = 'DEF' ) )

except select Z.TGT_LVL_ID as c1

from IND Z

where ( Z.GID = 'GHI ) )

except

select Z.TGT_LVL_ID as c1 from IND Z

where ( Z.GID = 'IJK' ) )

except

select Z.TGT_LVL_ID as c1 from IND Z

where ( Z.GID = 'LMN' ) )

except

select A.CNTCT as c1 from PRSN_D_CUST A

where ( A.STATE = 'FO' ) )

intersect

select Z.TGT_LVL_ID as c1

from IND Z

where ( Z.GID = 'IND_DFT_GLL_AUD' ) )

) D1) )

) D1

Thanks!!

I hve this insert qry which runs for a long time.This is cmplex qry but I thnk we can remove some unwantes case stmts.

Please suggest how to tune it or remove the unnecesaary prts.

eg:

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'XYZ' or

can be changed to:

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS in ('DEF') then 'XYZ'

else ‘Uns’ end = 'XYZ' or

INSERT INTO IND ( GID, TGT_LVL_ID )

select distinct '478b6a70-0be7-1000-8029-0a416d330000' as c1, D1.c1 as c2

from

(select A.CNTCT as c1

from PRSN_D_CUST A

where ( A.CNTCT in

(select distinct D1.c1 as c1 from

((((((((((((

(

(

(select distinct B.QLFD_ID as c1

from M_C_IND B

where ( B.GID = '123' )

intersect

select A.CNTCT as c1

from PRSN_D_CUST A

where ( A.PIN between '1' and '999999999' )

)

intersect

select distinct C.CNTCT as c1

from SUBPLAN_MNTHLY_CUST C

where ( 0 < (coalesce(C.BALANCE , 0)) )

)

except

select A.CNTCT as c1

from PRSN_D_CUST A

where ( (coalesce(( CAST((( TIMESTAMP '2011-04-01 00:00:00' - A.BIRTH_DT ) YEAR(4)) AS INTEGER) ) , 0)) < 18 )

)

except

select distinct A.CNTCT as c1

from

PRSN_D_CUST A,

PRSN_DX D /* Al_PRSN_DX (PRSN_DX) */ ,

POSITION_D E /* Dim_POSITION_D_WMA */ ,

POSITION_DX F /* Dim_POSITION_DWMA */

where (

A.PR_POSTN = E.POSITION_NAME and F.ROW = E.ROW

and A.CNTCT = D.ROW

and (

case when D.ENGAGED = 'Y' then 'Y' else 'N' end = 'Y' or

case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'

when F.ASGN_STS in ('LMN', 'OPQ', 'PQR', 'UST', 'STU', 'VWX') then 'N' else 'U' end = 'Y' or

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'XYZ' or

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'ABC'

or D.FNX2_ATRB_03 is not null

) ) )

except

(select A.CNTCT as c1

from

PRSN_D_CUST A

where ( case when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW' when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'A. HN'

or

case

when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'B. A I'

or

case

when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'I. UNC'

or

case

when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'J. Unknwn'

or case

when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn'

end = 'K TO' )

union

(select A.CNTCT as c1

from PRSN_D_CUST A

where (

case when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn' end = 'C. A II' )

intersect

select distinct X.CNTCT as c1

from PRSN_F_CUST X

where ( X.AS_OF_DT = 20110101

and 375000 < X.RA_GRA_ACM_AMT + X.SRA_GSRA_ACM_AMT + X.PAN_ACM_AMT + X.TSP_ACM_AMT + X.IRA_ACM_AMT + X.IAN_ACM_AMT + X.MFS_ACM_AMT + X.CSA_ACM_AMT ) )))

except

(select A.CNTCT as c1

from PRSN_D_CUST A

where ( (

case when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn' end = 'G. E A' or

case when A.M_SG_CD = 'a' then 'A. HN'

when A.M_SG_CD = 'b' then 'B. A I'

when A.M_SG_CD = 'c' then 'C. A II'

when A.M_SG_CD = 'd' then 'D. MMPHW'

when A.M_SG_CD = 'e' then 'E. MMP A I'

when A.M_SG_CD = 'f' then 'F. MMP A II'

when A.M_SG_CD = 'g' then 'G. E A'

when A.M_SG_CD = 'h' then 'H. MM

when A.M_SG_CD = 'u' then 'I. UNC'

when A.M_SG_CD = 't' then 'K TO'

else 'J. Unknwn' end = 'H. Mass Market'

)

and (A.CUSTOMER_STS = 'PU-IA' or A.CUSTOMER_STS = 'PU-IRA' or A.CUSTOMER_STS = 'PU-ONLY' or A.CUSTOMER_STS = 'PU-RETAIL') )

intersect

select distinct X.CNTCT as c1

from PRSN_F_CUST X

where ( X.AS_OF_DT = 20110401 and X.RA_GRA_ACM_AMT + X.SRA_GSRA_ACM_AMT + X.PAN_ACM_AMT + X.TSP_ACM_AMT + X.IRA_ACM_AMT + X.IAN_ACM_AMT + X.MFS_ACM_AMT + X.CSA_ACM_AMT < 10000 ) ))

except

select distinct A.CNTCT as c1

from PRSN_D_CUST A,

CLUSTER_D_CUST Y

where ( A.CLUSTER = Y.ROW

and (A.CUSTOMER_STS = 'IA-ONLY'

or A.CUSTOMER_STS = 'OTHER'

or A.CUSTOMER_STS = 'RETAIL'

or A.CUSTOMER_STS = 'TUITION-ONLY'

or

case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG' when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'

when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'

when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'

when Y.CST_ST_CD = 'e:AN' then '5. Ann'

when Y.CST_ST_CD = 'g:OT' then '6. Other'

else '7. Unknwn' end = '4. Non-Rtrmnt Services'

or

case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG' when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'

when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'

when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'

when Y.CST_ST_CD = 'e:AN' then '5. Ann'

when Y.CST_ST_CD = 'g:OT' then '6. Other'

else '7. Unknwn' end = '5. Ann'

or case

when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG'

when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'

when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'

when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'

when Y.CST_ST_CD = 'e:AN' then '5. Ann'

when Y.CST_ST_CD = 'g:OT' then '6. Other'

else '7. Unknwn' end = '6. Other'

or case when Y.CST_ST_CD = 'a:PP' then '1. Prem PYG'

when Y.CST_ST_CD = 'b:UA' then '2. Act Paid-Up'

when Y.CST_ST_CD = 'c:UI' then '3. Inact Paid-Up'

when Y.CST_ST_CD = 'd:NR' then '4. Non-Rtrmnt Services'

when Y.CST_ST_CD = 'e:AN' then '5. Ann'

when Y.CST_ST_CD = 'g:OT' then '6. Other'

else '7. Unknwn' end = '7. Unknwn') ) )

except

select Z.TGT_LVL_ID as c1 from IND Z

where ( Z.GID = 'ABC' ) )

except

select Z.TGT_LVL_ID as c1 from IND Z

where ( Z.GID = 'DEF' ) )

except select Z.TGT_LVL_ID as c1

from IND Z

where ( Z.GID = 'GHI ) )

except

select Z.TGT_LVL_ID as c1 from IND Z

where ( Z.GID = 'IJK' ) )

except

select Z.TGT_LVL_ID as c1 from IND Z

where ( Z.GID = 'LMN' ) )

except

select A.CNTCT as c1 from PRSN_D_CUST A

where ( A.STATE = 'FO' ) )

intersect

select Z.TGT_LVL_ID as c1

from IND Z

where ( Z.GID = 'IND_DFT_GLL_AUD' ) )

) D1) )

) D1

Thanks!!

4 REPLIES

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

06-23-2011
02:14 PM

06-23-2011
02:14 PM

Who is writing all those queries you post here?

Most of the source code is (how shall i put this) "overly complex" :-)

You probably don't need any of those CASEs, your example is exactly the same as:

where F.ASGN_STS in ('EFG', 'HIJ','DEF')

Dieter

Most of the source code is (how shall i put this) "overly complex" :-)

You probably don't need any of those CASEs, your example is exactly the same as:

where F.ASGN_STS in ('EFG', 'HIJ','DEF')

Dieter

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

06-23-2011
03:19 PM

06-23-2011
03:19 PM

You ar eright..they are complex..this s an oracle code cnvtd to teradata ..Not sure if a tool was used to do this..I I understand u...are you saying that we can use

where F.ASGN_STS in ('EFG', 'HIJ','DEF') instead of the entire statement given below.

But I cann se that one of the first statements itself is case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y', and you did not mention ABC or IJK.

Entires stataemnt:

case when D.ENGAGED = 'Y' then 'Y' else 'N' end = 'Y' or

case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'

when F.ASGN_STS in ('LMN', 'OPQ', 'PQR', 'UST', 'STU', 'VWX') then 'N' else 'U' end = 'Y' or

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'XYZ' or

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'ABC'

or D.FNX2_ATRB_03 is not null

where F.ASGN_STS in ('EFG', 'HIJ','DEF') instead of the entire statement given below.

But I cann se that one of the first statements itself is case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y', and you did not mention ABC or IJK.

Entires stataemnt:

case when D.ENGAGED = 'Y' then 'Y' else 'N' end = 'Y' or

case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'

when F.ASGN_STS in ('LMN', 'OPQ', 'PQR', 'UST', 'STU', 'VWX') then 'N' else 'U' end = 'Y' or

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'XYZ' or

case when F.ASGN_STS in ('PQR', 'STU') then ‘NWMG’

when F.ASGN_STS in ('EFG', 'HIJ') then 'XYZ'

when F.ASGN_STS = 'ABC' then 'ABC'

when F.ASGN_STS in ('UST', 'VWX') then 'VWX'

when F.ASGN_STS in ('LMN', 'OPQ') then ‘NWMG’

when F.ASGN_STS in ('DEF') then 'XYZ'

when F.ASGN_STS = 'IJK' then 'WMU'

else ‘Uns’ end = 'ABC'

or D.FNX2_ATRB_03 is not null

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

06-23-2011
03:37 PM

06-23-2011
03:37 PM

This must be a tool creating the SQL, no human mind will be so twisted.

where F.ASGN_STS in ('EFG', 'HIJ','DEF')

was for the CASE before the INSERT in your first post.

And there was no "case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'".

The new CASE is probably:

D.ENGAGED = 'Y'

OR F.ASGN_STS IN ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK')

OR D.FNX2_ATRB_03 is not null

Dieter

where F.ASGN_STS in ('EFG', 'HIJ','DEF')

was for the CASE before the INSERT in your first post.

And there was no "case when F.ASGN_STS in ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK') then 'Y'".

The new CASE is probably:

D.ENGAGED = 'Y'

OR F.ASGN_STS IN ('ABC', 'DEF', 'EFG', 'HIJ', 'IJK')

OR D.FNX2_ATRB_03 is not null

Dieter

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

06-23-2011
05:44 PM

06-23-2011
05:44 PM

LOL!!!! Ok..thats what I thought about the nw case..thank u so much for confirming!

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.