Replace query value with null when it has no data

[AbhinavJoshi]

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

[Kaviraj11 · Accepted solution]

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])

[Kaviraj11]

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]

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 · Accepted solution]

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 · Accepted solution]

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