Archives of the TeradataForum

Message Posted: Wed, 21 Sep 2005 @ 09:58:33 GMT


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

Subj:		Re: DW/ Query Man SQL Help with Max Version Please

From:		Victor Sokovin

I think I need an inner join but not sure how to do it. Working with waybill versions, I need to match a waybill version in one table to the max waybill version in the next table.

Not having the explain plan or statistics of data distribution in your tables I can only guess what *might* cause the problem.

Try to use a derived table to see if it helps reduce spool space consumption:

     SELECT COUNT (*)
     FROM VMVMT_CYCLE A
     , EVENT_ B
     , VWBM C
     , (SELECT D.WB_ID AS DT_WB_ID, MAX(D.WB_VRSN) AS DT_MAX_WB_VRSN
     FROM VWBM_CITY_CUST D
     GROUP BY 1) DT
      WHERE A.EQP_INIT = B.CAR_INIT
      AND A.EQP_NUMB = B.CAR_NUMB
      AND A.BGN_EVT_DT = B.EVT_DT
      AND A.BGN_EVT_CD = B.EVT_CD
      AND A.ORIG_STN_333 = B.STN_333
      AND B.WB_ID = C.WB_ID
      AND C.WB_ID = DT.DT_WB_ID
      AND C.WB_VRSN = DT.MAX_WB_VRSN

The syntax has not been tested!

Regards,

Victor