Archives of the TeradataForum

Message Posted: Wed, 21 Sep 2005 @ 12:27:13 GMT


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

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

From:		Henderson, John

Roberta,

I think you're getting a Cartesian Product. You point to the "D" table in your from clause, but don't specifically exclude any rows from the D table within your "where" clause. The result is that tables A, B, & C (limited to the results of your correlated sub-query) are being joined to every row of the D table. Try changing your query to the following:

     SELECT COUNT (*)

     FROM VMVMT_CYCLE A
     , EVENT_ B
     , VWBM C
     , (SELECT WB_ID
             , MAX(D.WB_VRSN) AS WB_VRSN
          FROM VWBM_CITY_CUST
         GROUP BY 1) D

     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_VRSN = D.WB_ID

Thanks,

John Henderson


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