13 Replies Latest reply: Jun 20, 2012 7:05 PM by Gordon Du RSS

Query Linking

Mona Karani
Currently Being Moderated

Hi all,

I have made below query to reflect the po's and grpo against it if any and purchase invoice against GRPO if any.
But if I am using inner join between por1 and pdn1 it is not reflecting open purchase orders and if i am using left join
than also it is not reflecting correct data.

Please Help!

Thanks-
Mona.

  • Re: Query Linking
    Gordon Du
    Currently Being Moderated

    Hi Mona,

     

    Post your query please.

     

    Thanks,

    Gordon

    • Re: Query Linking
      Mona Karani
      Currently Being Moderated

      Select T0.[Cardname] as 'CHKD', T0.[DocNum] as 'PO#',T0.[DocStatus] as 'PO Status', T1.[Quantity],T4.[DocNum],T3.[Quantity],T1.[Dscription] as 'Description'

      ,T1.[u_Ship_Date] as ' Expected Ship Date',T0.[U_Status], T6.[NumatCard] as 'Commercial Invoice No.',T0.[u_File_No],T0.[u_prodo_number] as 'Production Order', T0.[U_SO_Number] as 'Sales Order'  From OPOR t0 INNER JOIN [dbo].[POR1]  T1 ON T0.DocEntry = T1.DocEntry left  join [dbo].[PDN1]  T3 on t0.docEntry=T3.Baseentry inner JOIN OPDN T4 ON T3.DocEntry = T4.DocEntry left  join PCH1 T5 on t5.Baseentry=T4.DocEntry inner JOIN OPCH T6 ON T5.DocEntry = T6.DocEntry where 

      T0.[CardCode] = 'DU1000' or T0.[CardCode] = 'WU1020'

      Group By t0.docnum,T1.[Quantity],T0.[DocDate],T0.[DocTotal], T4.[DocNum], T1.[u_Ship_Date],T4.[DocNum],T3.[Quantity],T4.[DocDate],T4.DocTotal, T6.[DocNum], T6.[DocDate],T6.[DocTotal],T0.[Cardname],T4.[Cardname],T6.[Cardname],T0.[CardCode],T6.[NumatCard],T1.[Dscription],

      T0.[u_prodo_number], T0.[U_SO_Number],T0.[DocDueDate],T0.[u_File_No],T0.[U_Status],T0.[DocStatus] order by T0.[Cardname],T0.[DocStatus],T1.[u_Ship_Date]

      • Re: Query Linking
        Kennedy T
        Currently Being Moderated

        HI Mona,

         

        Try This

         

        Select Distinct T0.[Cardname] as 'CHKD', T0.[DocNum] as 'PO#',T0.[DocStatus] as 'PO Status', T1.[Quantity],T4.[DocNum] 'GRPO#',T3.[Quantity],T1.[Dscription] as 'Description',T6.[NumatCard] as 'Commercial Invoice No.'

        From OPOR t0 INNER JOIN POR1  T1 ON T0.DocEntry = T1.DocEntry

        left   outer join PDN1  T3 on t0.docEntry=T3.Baseentry

        left  outer JOIN OPDN T4 ON T3.DocEntry = T4.DocEntry

        left   outer join PCH1 T5 on t5.Baseentry=T4.DocEntry

        Left  outer JOIN OPCH T6 ON T5.DocEntry = T6.DocEntry

        where  T0.[CardCode] LIKE '%[%0]%'

         

         

        Regards

        Kennedy


         

         

         

  • Re: Query Linking
    Gordon Du
    Currently Being Moderated

    Try:

     

    Select Distinct T0.[Cardname] as 'CHKD', T0.[DocNum] as 'PO#',T0.[DocStatus] as 'PO Status', T1.[Quantity],T4.[DocNum] 'GRPO#',T3.[Quantity],T1.[Dscription] as 'Description',T6.[NumatCard] as 'Commercial Invoice No.'

    From OPOR t0

    INNER JOIN POR1  T1 ON T0.DocEntry = T1.DocEntry

    left  join PDN1  T3 on t0.docEntry=T3.Baseentry

    left JOIN OPDN T4 ON T3.DocEntry = T4.DocEntry

    left  join PCH1 T5 on t5.Baseentry=T4.DocEntry

    left JOIN OPCH T6 ON T5.DocEntry = T6.DocEntry

     

    where  T0.[CardCode] LIKE '%[%0]%'

  • Re: Query Linking
    Gordon Du
    Currently Being Moderated

    I am afraid the differences are coming from your UDF. Try my simplified query without UDF to see.

  • Re: Query Linking
    Zal Parchem
    Currently Being Moderated

    Hello Mona - I am going to agree 100% with Gordon on this one - you need to simplify your query to first get your results and then you can expand with UDFs and such.  LOL - I am going to add maybe might be best to have a bit of structure also to make it easier to read...

     

    Other Points:

     

    1.  You seem to be skipping some important table joins to get where you want to go - these are included below...

    2.  You need to put a bit more in your LEFT OUTER JOINS with Line Number and Item Code (otherwise it flies all over the place)...

    3.  I did not see any Returns or Credit Memos in your query, so I included them...these are pretty important aren't they???

    4.  You need to have "FOR BROWSE" for this many tables being utilized...

    5.  I would keep a clean copy of the below SQL and then slowly add new data fields to versions of the below SQL...testing frequently as you go along and keeping versions until you get to the point you want...

    6.  Thanks for your thread as I now have something to post for SAP site...

     

    Hope this helps you get on the right path...Zal

     

    ----COPY AND PASTE THE BELOW SQL---


    SELECT

     

    T2.DocNum AS 'Purch Ord',
    T2.DocDate AS ' Ord Date',
    T2.CardCode AS 'Vend Num',
    T1.ItemCode AS 'Product',
    T1.LineStatus AS 'Row Stat',
    T2.Canceled AS 'Cancelled?',
    T1.LineNum+1 AS 'Line',
    T1.Quantity AS 'Quantity',
    T1.OpenQty AS 'Open Qty',
    T4.DocNum AS 'Goods Rcpt',
    T4.DocDueDate AS 'Rcpt Date',
    T3.LineStatus AS 'Row Stat',
    T4.Canceled AS 'Cancelled?',
    T3.LineNum + 1 AS 'Line',
    T3.Quantity AS 'Quantity',
    T6.DocNum AS 'Return',
    T5.LineNum +1 AS 'Line',
    T5.LineStatus AS 'Row Stat',
    T6.Canceled AS 'Cancelled?',
    T5.Quantity AS 'Quantity',
    T8.DocNum AS 'AP Invoice',
    T7.LineNum + 1 AS 'Line',
    T7.Quantity AS 'Quantity',
    T10.DocNum AS 'AP Cred Memo',
    T9.LineNum + 1 AS 'Line',
    T9.Quantity AS 'Quantity'

     

    FROM POR1 T1

     

    LEFT OUTER JOIN OPOR T2
    ON T1.DocEntry = T2.DocEntry

     

    LEFT OUTER JOIN PDN1 T3
    ON T2.DocEntry = T3.BaseEntry
    AND T1.LineNum = T3.BaseLine
    AND T1.ItemCode = T3.ItemCode

     

    LEFT OUTER JOIN OPDN T4
    ON T3.DocEntry = T4.DocEntry

     

    LEFT OUTER JOIN RPD1 T5
    ON T4.DocEntry = T5.BaseEntry
    AND T3.LineNum = T5.BaseLine
    AND T3.ItemCode = T5.ItemCode

     

    LEFT OUTER JOIN ORPD T6
    ON T5.DocEntry = T6.DocEntry

     

    LEFT OUTER JOIN PCH1 T7
    ON T4.DocEntry = T7.BaseEntry
    AND T3.LineNum = T7.BaseLine
    AND T3.ItemCode = T7.ItemCode

     

    LEFT OUTER JOIN OPCH T8
    ON T7.DocEntry = T8.DocEntry

     

    LEFT OUTER JOIN RPC1 T9
    ON T8.DocEntry = T9.BaseEntry
    AND T7.LineNum = T9.BaseLine
    AND T7.ItemCode = T9.ItemCode

     

    LEFT OUTER JOIN ORPC T10
    ON T9.DocEntry = T10.DocEntry

     

    ORDER BY

     

    T2.DocNum,
    T1.LineNum,
    T4.DocNum,
    T3.LineNum,
    T6.DocNum,
    T5.LineNum,
    T8.DocNum,
    T7.LineNum,
    T10.DocNum,
    T9.LineNum

     

    FOR BROWSE

     

    Message was edited by: Zal Parchem

Actions