Conditional Where based on a Variable Value

jaredj34 · ‎09-30-2020

Looking for some help with writing a SQL query within PowerQuery. I plan to have at least 8 different saved queries that I will identify with Code variable. I would like to have a Case or other way to pick a WHERE clause based on the value of Code. I have tested this with just the WHERE p.partnumber in ("&SKUList&") and it works but now I am adding more WHERE Statements to choose from.

Here is the code I have so far:

let
    
    Code = List.First(#"tPickOne"[Code]) as text,
    SKUList = ("'"& Text.Combine(#"tMultiSKU"[SKU List],"','")&"'"),
    ItemTypeName = Text.Combine(#"tItemType"[Item Type Name],"','"),
    ItemStatus = Text.Combine(#"tItemStatus"[Item Status],"','"),
    Orderability = Text.Combine(#"tOrderability"[Orderability],"','"),
    
    
Source = Sql.Database("HOMEDEPOT", "AdHocReporting", [Query="SELECT DISTINCT      
p.partnumber as 'Part Number',
p.itemname as 'Description',
s.ItemTypeName as 'Item Type',
p.licensetypename as 'License Type',
p.SuperDivisionName as 'Super Division',
p.SuperDivisionCode as 'Super Division Code',
p.productfamilyname as 'Product Family',
p.productfamilycode as 'Product Family Code',
p.languagename as Language,
p.versionname as Version


FROM dbo.product p (NOLOCK)


CASE "&Code&" WHEN "A" THEN
	WHERE p.partnumber in("&SKUList&")
		AND p.itemstatusname in('"& ItemStatus & "')
ELSE
	WHERE p.itemstatusname in('"& ItemStatus & "')
		AND p.OrderabilityFlag in('"& Orderability &"')
END
"]),

Currently I am getting a Token Comma Expected error at the "A" in the CASE Function but I cannot figure out where or if it is needed. Does anyone have a good way to conditionally pick a Where Clause based on a parameter value?

Thanks,

Jared

Jimmy801 · ‎09-30-2020

Hello @jaredj34

as this A should be a text in the query text to SQL you have to write it like ""AA""

Hope this solves all your issues 🙂

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

jaredj34 · ‎10-01-2020

@Jimmy801 ,

Thanks for the help. The Token Comma Error is now gone but now I am getting an Incorrect syntax near keyword 'CASE' Error.

CASE "&Code&" WHEN ""A"" THEN
	WHERE p.partnumber in("&SKUList&")
		AND p.itemstatusname in('"& ItemStatus & "')
ELSE
	WHERE p.itemstatusname in('"& ItemStatus & "')
		AND p.OrderabilityFlag in('"& Orderability &"')
END

I am looking for any way to have a condtional Where statement based on the value of Code. When I get this test to work, I will be adding about 6 more variables and probably about 8 more values for Code. Should a CASE statement like this work? If not, any other ideas?

Thanks,

Jared