SAP Business One Query Generator- Text Line from RDR10

[manager2-ace]

Hi,

I'm a total newbie in SAP B1 and I'm trying to generate a very simple query on sales order to include text from RDR10.

I've tried the following:

SELECT

T0."DocNum", T0."CardName", T0."DocDueDate", T2."LineText"

FROM

ORDR T0 INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN RDR10 T2 ON T0."DocEntry" = T2."DocEntry"

WHERE

T0."CANCELED" ='N'

AND T0."DocDate" >=[%0]

AND T0."DocDate" <=[%1]

ORDER BY

T0."DocNum"

But if there are more than one line on the order, the output copies the text line for each one of them.

I've also tried with "DISTINCT" trying to avoid the duplicates and the red error appeared.

Is there a way to pick up the text line only once per order, please? Thank you

[DavidAndrusko]

Hello Claudio,

Could you include a screenshot of the error without customer data? For me the query runs without error (does not return any results). Also do note that even if you have Sales Orders in the ORDR table, this query will return no or limited results: only those orders that have a record available in the RDR10 table. Which concrete line field is it actually in Business One that you are trying to display? I could not immediately find it...

Regards,

David Andrusko
SAP Business One Support

[DavidAndrusko]

Hi Claudio,

You should be able to attach screenshot only using the "Insert File" icon. Pasted screenshots also don't appear for me. Correct, LEFT join should take the first ORDR table, and associate the records to it from the other table, as it fetches data if present in the left table. When in doubt, I always refer to this diagram:

https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server

I believe the issue is resolved right now. If not, then try checking this: https://stackoverflow.com/questions/8567288/sql-server-inner-join-with-distinct

I just don't know which field it is in SAP Business One.

Careful, T1."CANCELED" ='N in your second query is incorrect as the field does not exist in T1, but in T0.