Archives of the TeradataForum

Message Posted: Fri, 28 May 2010 @ 11:01:42 GMT

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

Hi. I think the problem is in non-uniqueness of the following fieds.

     CUST_ID, CUST_ACTN_DT ASC, NEW_CUST_ACT_DT.

What is the result of

     SELECT COUNT(1)
     FROM (
     SELECT CUST_ID, CUST_ACTN_DT, NEW_CUST_ACT_DT FROM (
     SELECT    A.CUST_ACTN_DT (FORMAT 'YYYY')(CHAR(4)) AS PERIOD,
                                A.CUST_ID, A.VEH_MK_NM, A.VEH_SRES_NM, A.VIN_NB,
                                A.CUST_ACTN_DT, B.VIN_NB AS NEW_VIN_NB,
                                B.CUST_ACTN_DT AS NEW_CUST_ACT_DT,
                                B.VEH_MK_NM AS NEW_VEH_MK_NM,
                                B.VEH_SRES_NM AS NEW_VEH_SRES_NM
                           FROM V_WOFDWVW.INT_SET_CUST_RETN A,
                                V_WOFDWVW.INT_SET_CUST_RETN B
                          WHERE A.VIN_NB <> B.VIN_NB
                            AND A.CUST_ID = B.CUST_ID
                            AND CAST(B.CUST_ACTN_DT AS DATE) BETWEEN CAST(A.CUST_ACTN_DT AS DATE)
                            AND ADD_MONTHS (CAST(A.CUST_ACTN_DT AS DATE),+120)

     ) mT
     GROUP BY 1, 2, 3
     HAVING COUNT(1) > 1) sT

query? If it's not zero then I am right.

The second - what are you trying to do? Your query is so complicated, mb you'll better say what you want in business language?

Thanks.

Best regards,

Dmitriy Boyko

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