Forum Discussion

oneillp111's avatar
oneillp111
Frequent Visitor
2 years ago
Solved

Use column from another table in SQL statement where condition

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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