CODEWITHSUNDEEP
sqlexcelstringwhere-clausepowerquery
Jared Johnson
Jared Johnson

Reputation: 11

Ignore portion of Where Clause if Variable is Null

I am looking for a way for my Where Clause in PowerQuery / SQL Statement to ignore NULL parameter values. I currently assign 4 different variables using Text.Combine to pull from an excel table. This works great but if any of the 4 variables are left blank, I get no results. Is it possible to ignore the p.partnumber portion of the Where Clause if SKUList is Null for example?

4 Variables:

SKUList = Text.Combine(#"tMultiSKU"[SKU List],"','"),
ItemTypeName = Text.Combine(#"tItemType"[Item Type Name],"','"),
ItemStatus = Text.Combine(#"tItemStatus"[Item Status],"','"),
Orderability = Text.Combine(#"tOrderability"[Orderability],"','")

Where Clause:

WHERE 
p.partnumber in ('"& SKUList & "')
AND s.ItemTypeName in('" & ItemTypeName & "')
AND p.itemstatusname in('"& ItemStatus & "')
AND p.OrderabilityFlag in('"& Orderability &"')

Upvotes: 1

Views: 154

Answers (1)

GMB
GMB

Reputation: 222432

I understand that you want to disable filter predicates when given an empty value.

You can use boolean logic. Assuming that you mean empty string (not null value), you would phrase the where clause as:

    WHERE 
        ('" & ItemTypeName & "' = '' OR s.ItemTypeName     = '" & ItemTypeName & "')
    AND ('" & ItemStatus   & "' = '' OR p.itemstatusname   = '" & ItemStatus   & "')
    AND ('" & Orderability & "' = '' OR p.OrderabilityFlag = '" & Orderability & "')

Note that I changed the IN condition to a simple equality, since that's what you seem to want.

This can easily be changed to handle null values instead, like:

('" & ItemTypeName & "' IS NULL OR s.ItemTypeName = '" & ItemTypeName & "')

Upvotes: 1

Related Questions