Using IIF in WHERE

Epicor ERP 10
1

IS it possible to use a IIF statement in the WHERE statement? For example?
IIF(@ReportType = 1 AND @Quantity = 0, THEN Erp.PlantWhse.PrimeBin = @Bin,
ELSE IF @ReportType = 1, THEN Erp.PlantWhse.PrimeBin = @Bin AND Erp.PartBin.OnHandQty >= @Quantity,
ELSE Erp.PartBin.BinNum = @Bin AND Erp.PartBin.OnHandQty >= @Quantity)

2

Not to dynamically change the Where criteria (as in which criteria to use). But I think your logic needs some work. It looks like you want three distinct Where clauses:

  1. Erp.PlantWhse.PrimeBin = @Bin
    (when @ReportType = 1 AND @Quantity = 0)

  2. Erp.PlantWhse.PrimeBin = @Bin AND Erp.PartBin.OnHandQty >= @Quantity
    (when @ReportType = 1 AND @Quantity <> 0)

  3. Erp.PartBin.BinNum = @Bin AND Erp.PartBin.OnHandQty >= @Quantity
    (when @ReportType <> 1)

So just roll your params into the individual where clauses, like

SELECT *
FROM T1
WHERE ((@ReportType = 1 AND @Quantity = 0 AND T1.PrimeBin = @Bin) 
  OR (@ReportType = 1 AND @Quantity <> 0 AND T1.PrimeBin = @Bin AND T1.OHQ >= @Quantity)
 OR (@ReportType = <> 1 AND T1.PrimeBin = @Bin AND T1.OHQ >= @Quantity))
1 Like
3

Are you talking about the SSRS query? If so, you can use a CASE statement, but you still need to get data first to filter. Also, your IIF statement is quite a bit off.
I find it simpler to filter the Tablix.

2 Likes