Archives of the TeradataForum

Message Posted: Thu, 19 Feb 2009 @ 20:09:16 GMT

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

I try to have more performance with this kind of SQL model. We spend too much time to get the 'last group of service', the 'last state' and the 'first service' information.

I know I can create a physical aggregate to fix this last-first image, but is not the way I understand Teradata use. It look like more ODS than datawarehousing. I can't use aggregate join operation because of derive table and those king of aggregate operation are not supported.

Any clue will be very appreciate for a lot of Teradata programmer.

Here the SQL model:

     REPLACE VIEW DB_NAME.ACTIVITY
     AS
     SELECT
        .........

     FROM DB_NAME.TACTIV

     INNER JOIN DB_NAME.TUSAG ON
        .........

     INNER JOIN DB_NAME.TSERV ON
        .........


     ---------- Last group of service    ----------

     LEFT JOIN ((SELECT A.NO,A.NO_ACTIV,A.NO_ASK,
                        MAX(R.NO_REGR_MES) NO_LAST_GROUP_SERV <========== aggregate operation
                 FROM   ........
                 GROUP BY 1,2,3) AS TNO_REGR <========== derive table

                INNER JOIN DB_NAME.TREGR_MES ON
                    .........
                    AND TNO_REGR.NO_LAST_GROUP_SERV = TREGR_MES.NO_REGR_MES)

     ON .....

     ---------- Last state  ----------

     LEFT JOIN ((SELECT E.NO,E.NO_ASK,A.NO_ACTIV,
                        MAX(E.NO_ETA_SERV) NO_LAST_STATE <========== aggregate operation
                 FROM   .........
                 GROUP  BY 1,2,3) AS TNO_ETA_SERV <========== derive table

                INNER JOIN DB_NAME.TETA_SERV ON
                    .........
                    AND TETA_SERV.NO_ETA_SERV = TNO_ETA_SERV.NO_LAST_STATE)

     ON .........

     ---------- First service ----------

     LEFT JOIN ((SELECT NO,NO_SERV,NO_ASK,
                        MIN(NO_SERV_ART_VENT) NO_FIRST_SERV <========== aggregate operation
                 FROM   .........
                 WHERE  COD_TYP_ALNEA = '1'  GROUP  BY 1,2,3) TNO_SERV_ART_VENT    <========== derive table

                INNER JOIN DB_NAME.TSERV_ART_VENT ON
                          .........
                    AND TNO_SERV_ART_VENT.NO_FIRST_SERV = TSERV_ART_VENT.NO_SERV_ART_VENT

     ON ...........
     ;

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