Use column from another table in SQL statement where condition

[oneillp111]

I have a small dashboard that has a sharepoint column of order numbers.  I am trying to use that column as the where condition in a SQL statement but I am not sure how to structure it.  The SQL works fine with the where statement hard coded but what I am trying to get working is the following

let
    Source = Oracle.Database("Datasource.world", [HierarchicalNavigation=true, Query="select VW_VBFA_B4.VBELN,#(lf) VW_VBFA_B4.VBELV,#(lf)  VW_VBAP_B4.MATNR,#(lf)  VW_VBAP_B4.POSNR,#(lf)  VW_VBAK_B4.KUNNR,#(lf)  VW_VBAK_B4.ERNAM#(lf)from VW_VBFA_B4 #(lf) inner join VW_VBAP_B4 on VW_VBFA_B4.VBELV = VW_VBAP_B4.VBELN #(lf)inner join VW_VBAK_B4 on VW_VBFA_B4.VBELV = VW_VBAK_B4.VBELN#(lf)WHERE VW_VBFA_B4.VBELN IN (COLUMN FROM OTHER QUERY)"])
in
    Source

 Any guidence would be great

[Anonymous · Accepted solution]

Hi @oneillp111 ,

Please try:

let
    // Load SharePoint list and get the order numbers as a list
    SharePointTable = ..., [Implementation="2.0"])[{0}][Items],
    OrderNumbersList = List.Distinct(SharePointTable[OrderNumber]),
    // Convert the list of order numbers to a comma-separated string
    OrderNumbersString = Text.Combine(OrderNumbersList, ","),
    
    // Construct the SQL query string
    SQLQuery = "select VW_VBFA_B4.VBELN,
                       VW_VBFA_B4.VBELV,
                       VW_VBAP_B4.MATNR,
                       VW_VBAP_B4.POSNR,
                       VW_VBAK_B4.KUNNR,
                       VW_VBAK_B4.ERNAM
                from VW_VBFA_B4
                inner join VW_VBAP_B4 on VW_VBFA_B4.VBELV = VW_VBAP_B4.VBELN
                inner join VW_VBAK_B4 on VW_VBFA_B4.VBELV = VW_VBAK_B4.VBELN
                WHERE VW_VBFA_B4.VBELN IN (" & OrderNumbersString & ")",

    // Connect to the Oracle database and execute the query
    Source = Oracle.Database("Datasource.world", [HierarchicalNavigation=true, Query=SQLQuery])
in
    Source

And note the formula.Firewall:
Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group