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-03-2011
11:20 AM

06-03-2011
11:20 AM

Hi!

I need help to tune this query . I have omiited somw "when' statements and put some dashes(--) to simplfy.

I am also not sure how the 2 'CASE WHEN ' statements work.

SELECT DISTINCT 'SB' AS c1 , D1.c1 AS c2 , D1.c3 AS c3 ,

D1.c2 AS c4

FROM (

SELECT COUNT ( T1."SR_NUM" ) AS c1 ,

CASE WHEN

CASE WHEN T1."AGE" <= 2 THEN '0-2'

---------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '0-2' THEN 0

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

--------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '3-4' THEN 3

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '5-6' THEN 5

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

-----------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '7-10' THEN 7

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

-------------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '11-20' THEN 11

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

---------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '21-50' THEN 21

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '>50' THEN 50

END AS c2 ,

CASE WHEN T1."AGE" <= 2 THEN '0-2'

WHEN T1."AGE" BETWEEN 3 AND 4 THEN '3-4'

------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END AS c3

FROM "POSTN_HIST" T4 , (

SELECT SF.OWN_POS AS OWN_POS , SD.SR_NUM AS SR_NUM ,

SD.STUS , SUM ( D.x_tiaa_business_day ) AS AGE ,

SF.OPN_DT , SF.CONTACT

FROM D_SD , DX SDX , F SF , DAY_D D

WHERE SD.ROW = SDX.ROW

AND SF.SR = SD.ROW

AND SDX.X_CLS_DT IS NULL

AND SF.OWN_POS <> 0

AND SDX.X_SR_CAT_TYPE_CD = 'SB'

AND SD.STUS <> 'Cnc'

AND D.ROW BETWEEN SF.OPN_DT AND 20110602

GROUP BY 1 , 2 , 3 , 5 , 6 ) T1

WHERE ( T4."ROW" = T1."OWN_POS"

AND T4."LVL_POSTN_ID" = '1-2YPMF1'

AND T1."OPN_DT" BETWEEN T4."EFF_DT" AND T4."END_DT" )

GROUP BY

CASE WHEN

CASE WHEN T1."AGE" <= 2 THEN '0-2'

WHEN T1."AGE" BETWEEN 3 AND 4 THEN '3-4'

-------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '0-2' THEN 0

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

--------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '3-4' THEN 3

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

-----------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '5-6' THEN 5

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

---------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '7-10' THEN 7

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

-------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '11-20' THEN 11

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

------------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '21-50' THEN 21

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

------------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '>50' THEN 50

END ,

CASE WHEN T1."AGE" <= 2 THEN '0-2'

---------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END ) D1

ORDER BY 4

Thanks!

I need help to tune this query . I have omiited somw "when' statements and put some dashes(--) to simplfy.

I am also not sure how the 2 'CASE WHEN ' statements work.

SELECT DISTINCT 'SB' AS c1 , D1.c1 AS c2 , D1.c3 AS c3 ,

D1.c2 AS c4

FROM (

SELECT COUNT ( T1."SR_NUM" ) AS c1 ,

CASE WHEN

CASE WHEN T1."AGE" <= 2 THEN '0-2'

---------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '0-2' THEN 0

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

--------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '3-4' THEN 3

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '5-6' THEN 5

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

-----------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '7-10' THEN 7

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

-------------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '11-20' THEN 11

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

---------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '21-50' THEN 21

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '>50' THEN 50

END AS c2 ,

CASE WHEN T1."AGE" <= 2 THEN '0-2'

WHEN T1."AGE" BETWEEN 3 AND 4 THEN '3-4'

------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END AS c3

FROM "POSTN_HIST" T4 , (

SELECT SF.OWN_POS AS OWN_POS , SD.SR_NUM AS SR_NUM ,

SD.STUS , SUM ( D.x_tiaa_business_day ) AS AGE ,

SF.OPN_DT , SF.CONTACT

FROM D_SD , DX SDX , F SF , DAY_D D

WHERE SD.ROW = SDX.ROW

AND SF.SR = SD.ROW

AND SDX.X_CLS_DT IS NULL

AND SF.OWN_POS <> 0

AND SDX.X_SR_CAT_TYPE_CD = 'SB'

AND SD.STUS <> 'Cnc'

AND D.ROW BETWEEN SF.OPN_DT AND 20110602

GROUP BY 1 , 2 , 3 , 5 , 6 ) T1

WHERE ( T4."ROW" = T1."OWN_POS"

AND T4."LVL_POSTN_ID" = '1-2YPMF1'

AND T1."OPN_DT" BETWEEN T4."EFF_DT" AND T4."END_DT" )

GROUP BY

CASE WHEN

CASE WHEN T1."AGE" <= 2 THEN '0-2'

WHEN T1."AGE" BETWEEN 3 AND 4 THEN '3-4'

-------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '0-2' THEN 0

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

--------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '3-4' THEN 3

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

-----------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '5-6' THEN 5

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

---------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '7-10' THEN 7

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

-------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '11-20' THEN 11

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

------------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '21-50' THEN 21

WHEN CASE

WHEN T1."AGE" <= 2 THEN '0-2'

------------------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END = '>50' THEN 50

END ,

CASE WHEN T1."AGE" <= 2 THEN '0-2'

---------------------------

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END ) D1

ORDER BY 4

Thanks!

3 REPLIES

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

06-06-2011
02:43 PM

06-06-2011
02:43 PM

Who wrote that query?

This is one of the most nonsens CASEs if ever seen :-)

It's just two simple age group calculations:

CASE

WHEN T1."AGE" <= 2 THEN 0

...

WHEN T1."AGE" BETWEEN 21 AND 50 THEN 21

ELSE 50

END as c2

,

CASE

WHEN T1."AGE" <= 2 THEN '0-2'

...

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END as c3

and in Teradata you can use an alias in any place, so the GROUP BY is reduced to:

GROUP BY c2,c3

Dieter

This is one of the most nonsens CASEs if ever seen :-)

It's just two simple age group calculations:

CASE

WHEN T1."AGE" <= 2 THEN 0

...

WHEN T1."AGE" BETWEEN 21 AND 50 THEN 21

ELSE 50

END as c2

,

CASE

WHEN T1."AGE" <= 2 THEN '0-2'

...

WHEN T1."AGE" BETWEEN 21 AND 50 THEN '21-50'

ELSE '>50'

END as c3

and in Teradata you can use an alias in any place, so the GROUP BY is reduced to:

GROUP BY c2,c3

Dieter

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

06-10-2011
02:29 PM

06-10-2011
02:29 PM

I did change this but I do not see any improvement in the performance of the query. The IOcount , CPUTIME and spool usage have all increased a little bit by the chnages. IS there any way to tune it?

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

06-13-2011
02:04 AM

06-13-2011
02:04 AM

Hard to tell without additional info about DDL/stats/explain/DBQL.

But my guess would be the inner query to calculate the "age", this is probably a product join due to the BETWEEN creating a large intermediate result set before doing the SUM.

There should be another column in your calendar table based on the calculation of:

SUM (x_tiaa_business_day) OVER (ORDER BY x_tiaa_business_day ROWS UNBOUNDED PRECEDING)

Now you need two joins to the calendar table, but both are equi-joins on OPN_DT and 20110602 and the AGE is a simple difference.

In TD13 the join on 20110602 could be rewritten as a Scalar Subquery:

(SELECT xxx FROM calendar WHERE calendardate = 20110602)

Dieter

But my guess would be the inner query to calculate the "age", this is probably a product join due to the BETWEEN creating a large intermediate result set before doing the SUM.

There should be another column in your calendar table based on the calculation of:

SUM (x_tiaa_business_day) OVER (ORDER BY x_tiaa_business_day ROWS UNBOUNDED PRECEDING)

Now you need two joins to the calendar table, but both are equi-joins on OPN_DT and 20110602 and the AGE is a simple difference.

In TD13 the join on 20110602 could be rewritten as a Scalar Subquery:

(SELECT xxx FROM calendar WHERE calendardate = 20110602)

Dieter

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.