Executing an if type statement in the sql where clause

Question

I am trying to make some kind of "if" statement in the where section. I understand that sql does not support this, but I am sure there must be some way to make this work with sql syntax. As shown in the area in bold, I try to find all the elements starting with d and filter them out if their userfld2 also = container.

Is there a smarter way to do this than I do, or am I not up to date?

Thanks in advance.

Select a.ItemID , b.ConversionFactor VCaseAmt , sum(c.ConversionFactor + 1) SCaseAmt , a.status , a.UserFld2 From timItem a inner join timItemUnitOfMeas b on a.ItemKey = b.ItemKey and b.TargetUnitMeasKey = 115 left join timItemUnitOfMeas c on a.ItemKey = c.ItemKey and c.TargetUnitMeasKey = 116 left join timItemUnitOfMeas d on a.ItemKey = d.ItemKey and d.TargetUnitMeasKey = 126 Where d.TargetUnitMeasKey is null and b.ConversionFactor != c.ConversionFactor + 1 and a.Status = 1 and **(filter a.itemid not like 'd%' when a.userfld2 = 'Container')** Group by a.ItemID, b.TargetUnitMeasKey, b.ConversionFactor, C.TargetUnitMeasKey , c.ConversionFactor, a.status, a.UserFld2 Order by a.ItemID 

Answer 1

Use this:

 Select a.ItemID, b.ConversionFactor VCaseAmt, sum(c.ConversionFactor + 1) SCaseAmt, a.status, a.UserFld2 From timItem a inner join timItemUnitOfMeas b on a.ItemKey = b.ItemKey and b.TargetUnitMeasKey = 115 left join timItemUnitOfMeas c on a.ItemKey = c.ItemKey and c.TargetUnitMeasKey = 116 left join timItemUnitOfMeas d on a.ItemKey = d.ItemKey and d.TargetUnitMeasKey = 126 Where d.TargetUnitMeasKey is null and b.ConversionFactor != c.ConversionFactor + 1 and a.Status = 1 and not (a.itemid like 'd%' AND a.userfld2 = 'Container') Group by a.ItemID, b.TargetUnitMeasKey, b.ConversionFactor, C.TargetUnitMeasKey, c.ConversionFactor, a.status, a.UserFld2 Order by a.ItemID