Archives of the TeradataForum

Message Posted: Fri, 27 Oct 2000 @ 14:17:02 GMT

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

The parser thought you were trying to outer join t5 to t3 without referencing a condition on which to do that. Also, the first "distinct" in the query was unnecessary because you were grouping by those columns anyway.

      SELECT
         T1.BENEFICIARYID
        ,T2.SOCIALSECURITYNUMBER
        ,T1.BENEFICIARYNAME
        ,T3.ATTENTIONLINE
        ,T4.STREET
        ,T4.CITY
        ,T4.STATE
        ,T4.ZIP
        ,COUNT(DISTINCT T5.ELIGIBLE_MONTH)
        FROM MSAUSERVIEW.VBENEFICIARYACTIVITY T1
       INNER JOIN MSAUSERVIEW.VBENEFICIARY T2
          ON T1.BENEFICIARYID = T2.BENEFICIARYID
        LEFT JOIN MSAUSERVIEW.VBENEFICIARYATTENTION T3
          ON T2.BENEFICIARYID = T3.BENEFICIARYID
         AND T3.ATTENTIONTYPE = 'C'
       INNER JOIN MSAUSERVIEW.VBENEFICIARYADDRESS T4
          ON T2.BENEFICIARYID = T4.BENEFICIARYID
         AND T4.ADDRESSTYPECODE = 'C'
       INNER JOIN MSAUSERVIEW.VELIGIBLEMONTH T5
          ON T2.BENEFICIARYID = T5.BENEFICIARYID
         AND T5.ELIGIBLE_MONTH BETWEEN '200001' AND '200012'
         AND T5.COVERAGE = 'J'
       GROUP BY 1,2,3,4,5,6,7,8
      ;

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