Skip to Content

How to use WDD1 table with other tables without duplication of data in SAP B1 9.2?.

Hi all,

I have a problem in query, I want to design report which will show column of approval date where I used WDD1 table and CreateDate field but if I include the WDD1 table in my query it duplicates data. My query is the following

SELECT T0.[DocNum], T0.[CardName], T1.[Dscription], T0.[DocDate], T0.[TaxDate], T1.[U_abv]gr, T1.[PriceAfVAT], T1.[U_diff], T1.[U_pat], T2.[DflAccount], T3.[BankName], T5.[CreateDate] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN ODSC T3 ON T2.[DflBankKey] = T3.[AbsEntry] INNER JOIN OUSR T4 ON T0.[UserSign] = T4.[USERID] INNER JOIN WDD1 T5 ON T4.[USERID] = T5.[UserID] WHERE T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

How can I solve duplication of data in query?

Please anyone can help me.
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 15, 2016 at 04:14 PM

    Hi Rurangwa,

    Your inner join with WDD1 is incorrect. You should conect your document with OWDD and then connect the WDD1 with OWDD. Of course if you have more than one authorizer for one document, the results will be replicate by the number of authorizers.

    Your query with adjustments:

    SELECT 
    	T0.[DocNum], 
    	T0.[CardName], 
    	T1.[Dscription], 
    	T0.[DocDate], 
    	T0.[TaxDate], 
    	T1.[U_abv], 
    	T1.[PriceAfVAT], 
    	T1.[U_diff], 
    	T1.[U_pat], 
    	T2.[DflAccount], 
    	T3.[BankName], 
    	T6.[CreateDate] 
    FROM 
    	OPDN T0 
    	INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
    	INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] 
    	LEFT JOIN ODSC T3 ON T2.[DflBankKey] = T3.[AbsEntry] 
    	INNER JOIN OUSR T4 ON T0.[UserSign] = T4.[USERID] 
    	INNER JOIN OWDD T5 ON T5.DocEntry = T0.DocEntry AND T5.ObjType = T0.ObjType
    	INNER JOIN WDD1 T6 ON T6.WddCode = T5.WddCode
    WHERE 
    	T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]
    

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded