 |
 |
Archives of the TeradataForum
Message Posted: Fri, 20 Aug 2010 @ 21:04:57 GMT
| Subj: | | BTEQ Question from 6.0 to 12.0 |
| |
| From: | | Wojno, Thomas |
Migrating from 6.0 to 12 .0 , Have a report that is not functioning the same in 12 as it did in 6. Seems to be the WITH CLAUSE.
The Subtotals print one per line rather than across as it did on 6.0
before
10/08/20 23 Fri-Fri 4120 53 2090 7777 88 2222
after
10/08/20 23 Fri-Fri 4120 53 2090 7777 88 2090
SEL
current_date (TITLE''),
' ' (TITLE ''),
CASE
WHEN (current_date - DATE '1980-11-02') MOD 7 = 6 THEN 'Sat-Sat'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 0 THEN 'Sun-Sun'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 1 THEN 'Mon-Mon'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 2 THEN 'Tue-Tue'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 3 THEN 'Wed-Wed'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 4 THEN 'Thr-Thr'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 5 THEN 'Fri-Fri' END (TITLE ''),
' ' (TITLE''),
current_date-7 (TITLE''),
' ' (TITLE''),
current_date-6 (TITLE''),
' ' (TITLE''),
current_date-5 (TITLE''),
' ' (TITLE''),
current_date-4 (TITLE''),
' ' (TITLE''),
current_date-3 (TITLE''),
' ' (TITLE''),
current_date-2 (TITLE''),
' ' (TITLE''),
current_date-1 (TITLE''),
' ' (TITLE''),
current_date (TITLE'');
SELECT
current_date,
tme (TITLE 'hour',FORMAT '99'),
CASE
WHEN (current_date - DATE '1980-11-02') MOD 7 = 6 THEN 'Sat-Sat'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 0 THEN 'Sun-Sun'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 1 THEN 'Mon-Mon'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 2 THEN 'Tue-Tue'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 3 THEN 'Wed-Wed'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 4 THEN 'Thr-Thr'
WHEN (current_date - DATE '1980-11-02') MOD 7 = 5
THEN 'Fri-Fri' END (TITLE 'From-To'),
MAX (CASE WHEN calendar_date = current_date - 7
THEN busy
ELSE NULL END) as date_7,
MAX (CASE WHEN calendar_date = current_date - 6
THEN busy
ELSE NULL END) as date_6,
MAX (CASE WHEN calendar_date = current_date - 5
THEN busy
ELSE NULL END) AS date_5,
MAX (CASE WHEN calendar_date = current_date - 4
THEN busy
ELSE NULL END) AS date_4,
MAX (CASE WHEN calendar_date = current_date - 3
THEN busy
ELSE NULL END) AS date_3,
MAX (CASE WHEN calendar_date = current_date - 2
THEN busy
ELSE NULL END) AS date_2,
MAX (CASE WHEN calendar_date = current_date - 1
THEN busy
ELSE NULL END) AS date_1,
MAX (CASE WHEN calendar_date = current_date
THEN busy
ELSE NULL END) AS run_date
FROM (
SELECT
cal.calendar_date,
EXTRACT (HOUR FROM RES.starttime) tme,
COUNT(*) AS busy
FROM
sys_calendar.calendar cal,
tperf.dbqlogtbl_hst res
WHERE
(calendar_date BETWEEN current_date - 7 AND current_date )
AND
cal.calendar_date = res.LOGdate
AND
errorcode = 0
GROUP BY 1,2) x
GROUP BY 1,2,3
ORDER BY 1,2,3
WITH
SUM(CASE WHEN calendar_date = current_date - 7
THEN busy ELSE NULL
END)(TITLE ''),
SUM(CASE WHEN calendar_date = current_date - 6
THEN busy ELSE NULL
END)(TITLE ''),
SUM(CASE WHEN calendar_date = current_date - 5
THEN busy ELSE NULL
END)(TITLE ''),
SUM(CASE WHEN calendar_date = current_date - 4
THEN busy ELSE NULL
END)(TITLE ''),
SUM(CASE WHEN calendar_date = current_date - 3
THEN busy ELSE NULL
END)(TITLE ''),
SUM(CASE WHEN calendar_date = current_date - 2
THEN busy ELSE NULL
END)(TITLE ''),
SUM(CASE WHEN calendar_date = current_date - 1
THEN busy ELSE NULL
END)(TITLE ''),
SUM(CASE WHEN calendar_date = current_date
THEN busy ELSE NULL
END)(TITLE '')
;
| |
