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.

[manager2-ace]

Hi David,

the error shows up only if I add the "DISTINCT" instruction, I tried to paste the cropped screenshot but it doesn't show up in my answer.

[former_member775661]

Hi manager2_ace,

Try this one

ORDR T0 INNER JOIN RDR10 T1 ON T0.DocEntry = T1.DocEntry

This will work perfectly if your documents consist of one line text per item .

[manager2-ace]

Thank you Precious MGC,

we only add one text line at the bottom of the orders, I finally got it working as follows:

SELECT

T0."DocEntry", T0."DocNum", T0."CardName", T0."DocDate", T0."DocDueDate", T1."LineText"

FROM

ORDR T0 LEFT OUTER JOIN RDR10 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE

T1."CANCELED" ='N'

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

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

ORDER BY

T0."DocEntry"

This is the output I was looking for, all text fields line from orders into selected date range. Even if the text field is blank.

[former_member775661]

That's good to hear manager2_ace. :)))