Archives of the TeradataForum

Message Posted: Tue, 24 Feb 2009 @ 16:17:42 GMT

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

Like you see, english is not my native language, so may be I exposed the problem to simplistic ;-)

I have to build a view with join. In those join, I have to use the maximum or minimum operation.

The result of the maximum or minimum operation are keep in a derive table and join to an another Table. And finally, all the information are send to the on-line application.

It take us to much time to exexute this maximum - minimum operation.

I didn't want a physical table to fix this performance issue, but I don't think I will have the choice.

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
                 FROM   ........
                 GROUP BY 1,2,3) AS TNO_REGR

                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

                 FROM   .........
                 GROUP  BY 1,2,3) AS TNO_ETA_SERV

                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

                 FROM   .........
                 WHERE  COD_TYP_ALNEA = '1'  GROUP  BY 1,2,3)
                          AS TNO_SERV_ART_VENT

                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>>