Archives of the TeradataForum

Message Posted: Thu, 19 Jun 2014 @ 08:51:31 GMT

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

James,

There is no explicit join between VIEW1 and VIEW2 hence cross (production ) join.

     SELECT
       FROM VWDB.VIEW1
            , VWDB.VIEW2
            RIGHT JOIN VWDB.TAX_ACCOUNT_LIST
                    ON VWDB.TAX_ACCOUNT_LIST.SMRT_INTERNAL_ID=VWDB.VIEW2.SMRT_INTERNAL_ID
                   AND VWDB.TAX_ACCOUNT_LIST.ACCT_TYPE_CD=VWDB.VIEW2.ACCT_TYPE_CD
                   AND VWDB.TAX_ACCOUNT_LIST.CR_NUM=VWDB.VIEW2.CR_NUM
             LEFT JOIN VWDB.TAX_ACCT_FINANCIAL_TRANSACTION
                    ON VWDB.TAX_ACCOUNT_LIST.SMRT_INTERNAL_ID=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.SMRT_INTERNAL_ID
                   AND VWDB.TAX_ACCOUNT_LIST.ACCT_TYPE_CD=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.ACCT_TYPE_CD
                   AND VWDB.TAX_ACCOUNT_LIST.CR_NUM=VWDB.TAX_ACCT_FINANCIAL_TRANSACTION.CR_NUM
            INNER JOIN VWDB.TAX_ACCT_FINANCIAL_TRANS_LIST
                    ON VWDB.TAX_ACCT_FINANCIAL_TRANS_LIST.SMRT_INTERNAL_ID=VWDB.VIEW1.SMRT_INTERNAL_ID
            INNER JOIN VWDB.VIEW1_List
                    ON VWDB.VIEW1_List.SMRT_INTERNAL_ID=VWDB.VIEW1.SMRT_INTERNAL_ID
             LEFT JOIN VWDB.VIEW3
                    ON VWDB.VIEW1_List.SMRT_INTERNAL_ID=VWDB.VIEW3.SMRT_INTERNAL_ID
      WHERE (VWDB.VIEW3.RELSHP_END_DT IN ({d '2010-12-31'})
             AND VWDB.VIEW1.ID_TYPE_CD IN (2)
             AND (VWDB.VIEW1.PRIM_ID_IND  =  'n'
                  OR VWDB.VIEW1.STAT_CD  <>  1
                  OR VWDB.VIEW2.PRD_STATUS_CD  <>  6
                  OR VWDB.VIEW2.DW_VER_END_DT  <>  {d '9999-12-31'}
                  OR VWDB.VIEW2.ACCT_TYPE_CD  <>  100
                  OR VWDB.VIEW2.TAX_PRD_END_DT  <>  {d '9999-12-31'}))

Yong Boon, Lim
Database Administrator
IBM Global Technology Services

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