Skip to Content
author's profile photo Former Member
0
Former Member

How to avoid Duplication in query output

Posted on Aug 22, 2016 at 06:34 AM 20 Views

This is a simple query I've wrote for finding Customer Information.There are duplication in this query if the more than one contact persons entered to the fields(OCPR) .How can i avoid this from the output??Please see the sample output attached for any reference. SELECT T0.[CardCode], T0.[CardName],OCRG.GroupName,T0.Password AS 'Product& Year',OSHP.TrnspName as 'Constitution',CRD7.TaxId0,CRD7.TaxId1,CRD7.TaxId11,CRD7.ECCNo,CRD7.CERegNo,CRD7.CERange,CRD7.CEDivis,CRD7.CEComRate,OOND.IndName,OSLP.SlpName, T1.[Name],T1.[FirstName], T1.[MiddleName], T1.[LastName], T1.[Position], T1.[Address], T1.[Tel1], T1.[Tel2], T1.[Cellolar], T1.[Fax], T1.[E_MailL], T1.[Pager], T1.[Notes1] as 'Bday', T1.[Notes2] as 'Wedding' FROM OCRD T0 INNER JOIN OCPR T1 ON T0.CardCode = T1.CardCode INNER JOIN OSLP ON T0.SLPCODE = OSLP.SLPCODE INNER JOIN OCRG ON T0.GroupCode = OCRG.GroupCode INNER JOIN OOND ON T0.IndustryC = OOND.IndCode INNER JOIN CRD7 ON T0.CardCode = CRD7.CardCode INNER JOIN OSHP ON T0.ShipType = OSHP.TrnspCode WHERE T0.CardType = '[%0]'

sasAS.txt.zip (33.9 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo careLess aucim
    careLess aucim
    Posted on Aug 22, 2016 at 06:45 AM
    1

    Hi Nabil,

    I think the only way not to duplicate the row in your output is to delete the other contact saved in your OCPR table..

    Regards.

    Add a comment
    10|10000 characters needed characters exceeded

    • careLess aucim Former Member
      Aug 22, 2016 at 07:06 AM

      Hi Nabil,

      Yah distinct functions can remove duplicates but theres no way to use distinct function in your case. Because you want to display all the contacts and contact is link in the bp table. If you want use crystal report to get around with it use the supress function or you group it.

    Comment on This Answer
  • author's profile photo Kennedy T
    Kennedy T
    Posted on Aug 22, 2016 at 07:00 AM
    1

    Hi Nabil.

    Write sub query for the contact person instead of linking to main query...

    You can also use distinct but no use i think...

    Cheers!

    Add a comment
    10|10000 characters needed characters exceeded

    Comment on This Answer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.