Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 20 Aug 2010 @ 21:04:57 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


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 '')
     ;


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016