Forum Discussion

AbhinavJoshi's avatar
AbhinavJoshi
Responsive Resident
2 years ago
Solved

Replace query value with null when it has no data

Hello,

 

I am passing a list of values from a query to my sql query in where clause. The problem is when the value is null, it gives me incorrect syntax error. Any ideas how to fix this?
My Query in Source

= Sql.Database("server", "db", [Query="Select val1, val2, val3,  from table INNER JOIN table2 ON table.id = table1.oid where CONCAT(table.val, table1.val1) in ("& Text.Combine(UniqueCerts,",") &") and val3= 'ABC' "])

 

The Highleted text causes an error when UniqueCerts value is blank. I would like to replace it with some value if it is null or some workaround.

 

Thanks

Need Help
Show and Tell
  • Kaviraj11's avatar
    Kaviraj11
    2 years ago

    Got it, adjusted the query below:

     

    let
    DefaultCert = "'N/A'", // Ensure the default value is properly quoted
    FilteredCerts = List.Select(UniqueCerts, each _ <> null),
    CertsList = if List.Count(FilteredCerts) > 0 then Text.Combine(FilteredCerts, ",") else DefaultCert,
    Query = "Select val1, val2, val3 from table INNER JOIN table2 ON table.id = table1.oid where CONCAT(table.val, table1.val1) in (" & CertsList & ") and val3= 'ABC'"
    in
    Sql.Database("server", "db", [Query=Query])

  • AbhinavJoshi's avatar
    AbhinavJoshi
    2 years ago

    This would work but I figured it out the best option is to skip the sql query as a whole if the UniqueCerts list is empty, and used an empty default table. Thanks for your help

4 Replies

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    2 years ago

    Hi,

     

    can you try with the code below:

     

    let
    FilteredCerts = List.Select(UniqueCerts, each _ <> null),
    Query = "Select val1, val2, val3 from table INNER JOIN table2 ON table.id = table1.oid where CONCAT(table.val, table1.val1) in (" & Text.Combine(FilteredCerts, ",") & ") and val3= 'ABC'"
    in
    Sql.Database("server", "db", [Query=Query])

    • AbhinavJoshi's avatar
      AbhinavJoshi
      Responsive Resident
      2 years ago

      Hi Kaviraj11 ,
      This won't work as the FilteredCerts would still be null if the UniqueCerts has no values. It would cause the same error.

      • Kaviraj11's avatar
        Kaviraj11
        Solution Sage
        2 years ago

        Got it, adjusted the query below:

         

        let
        DefaultCert = "'N/A'", // Ensure the default value is properly quoted
        FilteredCerts = List.Select(UniqueCerts, each _ <> null),
        CertsList = if List.Count(FilteredCerts) > 0 then Text.Combine(FilteredCerts, ",") else DefaultCert,
        Query = "Select val1, val2, val3 from table INNER JOIN table2 ON table.id = table1.oid where CONCAT(table.val, table1.val1) in (" & CertsList & ") and val3= 'ABC'"
        in
        Sql.Database("server", "db", [Query=Query])