cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leeeeeevin
New Member

SAP Business One Query

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

 

2 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

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:

merge.JPG

 

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.

View solution in original post

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: 

 

Screenshot 2021-03-03 084141.gif

Maybe it will help someone who has the same problem

 

Best regards and thank you!

Levin

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

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:

merge.JPG

 

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.

View solution in original post

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: 

 

Screenshot 2021-03-03 084141.gif

Maybe it will help someone who has the same problem

 

Best regards and thank you!

Levin

View solution in original post

Icey
Community Support
Community Support

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors