SAP Business One Query

[Leeeeeevin]

Hello,

i have a problem with a SQL-Query from SAP Business one. 

I have 4 tables: 

ORDR - This is the sales order table

RDR1 - These are the sales order lines 

ODLN - This is the delivery table

DLN1 - These are the delivery lines 

In SQL i do the join like this:

SELECT DISTINCT

T1.DocNum, T1.DocDate, T1.CardCode,
T1.DocEntry AS 'ORDR DocEntry',
T0.LineNum AS 'RDR1 LineNum',
T0.ItemCode,
T2.BaseEntry AS 'DLN1 BaseEntry',
T2.BaseLine AS 'DLN1 BaseLine',
T2.ItemCode,
T0.TrgetEntry AS 'RDR1TrgetEntry',
T3.DocEntry AS 'ODLN DocEntry'

FROM RDR1 T0 

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

LEFT OUTER JOIN DLN1 T2
ON T1.DocEntry = T2.BaseEntry
AND T0.LineNum = T2.BaseLine
AND T0.ItemCode = T2.ItemCode

LEFT OUTER JOIN ODLN T3
ON T2.DocEntry = T3.DocEntry

The problem now is the red part. How can i map this in PowerBi?

Thank you very much 

Levin

[Icey · Accepted solution]

Hi @Leeeeeevin ,

Does the red code mean that left outer join two tables based on three columns?

If so, hold "Ctrl" and select matching columns one by one like so:

If this is not what you want, please provide more details.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Icey · Accepted solution]

Hi @Leeeeeevin ,

Does the red code mean that left outer join two tables based on three columns?

If so, hold "Ctrl" and select matching columns one by one like so:

If this is not what you want, please provide more details.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Leeeeeevin · Accepted solution]

Hey Icey, 

sometimes the solution is easier than you think 😅. Thank you very much! 

I also found another option to put the SQL-Query in PowerBi: 

Maybe it will help someone who has the same problem

Best regards and thank you!

Levin

[Icey]

Hi @Leeeeeevin ,

Thanks for sharing another solution!😉 You can also accept your reply as a solution, too. Your contribution is highly appreciated.

Best Regards,

Icey