Archives of the TeradataForum

Message Posted: Mon, 24 Oct 2011 @ 18:27:15 GMT


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

Subj:		Having clause

From:		GRAHAM, Paul

I'm trying to return all entries for a query which have more than duplicate entries in a given field, the script I'm trying (below) returns rows where there are no duplicates in the field. Any suggestions on how to get the query to pull back results where there are duplicates in the PARTY_ID field:

     SELECT TOP 100
     T1.PARTY_ID
     ,T1.CUSTOMER_PRODUCT_REF_NUMBER AS Membership_Number ,T1.PRODUCT_STATUS_CODE
     ,T2.GROUP_NAME ,T3.SWIFT_MEMBER_LIFETIME_NUMBER ,T5.GROUP_NUMBER ,T4.SOURCE_SYSTEM_CODE
     ,T6.PERSON_FORENAME ,T6.PERSON_SURNAME ,T1.PRODUCT_DESCRIPTION FROM
     PROD_B_UKM_USER_VIEWS.PARTY_ROLE_PRODUCTS T1
         ,PROD_B_UKM_USER_VIEWS.GROUPS         T2
         ,PROD_B_UKM_USER_VIEWS.PERSON_XREFS     T3
         ,PROD_B_UKM_USER_VIEWS.GROUP_XREFS      T4
         ,PROD_A_UKM_SWFT_USER_VIEWS.GROUPS     T5
         ,PROD_B_UKM_USER_VIEWS.PERSONS        T6

     WHERE T1.GROUP_ID = T2.GROUP_ID
     AND   T1.PARTY_ID = T3.PERSON_PARTY_ID
     AND  T1.GROUP_ID = T4.GROUP_ID
     AND  T4.SOURCE_GROUP_ID = T5.GROUP_PARTY_ID AND  T1.PARTY_ID = T6.PERSON_PARTY_ID AND
     T3.SWIFT_MEMBER_LIFETIME_NUMBER IS NOT NULL
     AND   T1.PRODUCT_STATUS_CODE = 'EXISTING'
     GROUP BY
     T1.PARTY_ID
     ,T1.CUSTOMER_PRODUCT_REF_NUMBER
     ,T1.PRODUCT_STATUS_CODE
     ,T2.GROUP_NAME
     ,T3.SWIFT_MEMBER_LIFETIME_NUMBER
     ,T5.GROUP_NUMBER
     ,T4.SOURCE_SYSTEM_CODE
     ,T6.PERSON_FORENAME
     ,T6.PERSON_SURNAME
     ,T1.PRODUCT_DESCRIPTION
     HAVING COUNT( T1.PARTY_ID) >1

Thanks in Advance

Paul G
Operational Analyst


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

Attachments

Library

Quick Reference