CODEWITHSUNDEEP
sql
Aarmora
Aarmora

Reputation: 1153

Doing an "if" type statement in a sql where clause

I'm trying to do some sort of "if" statement in my where clause. I realize that sql doesn't support this but I'm sure there must be some way to make this work with sql syntax. As shown in the area I have in bold I'm trying to find all items that begin with d and filter them out if their userfld2 also = container.

Is there a more reasonable way to do this than I am doing or am I way off the mark?

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

Upvotes: 6

Views: 163

Answers (1)

Lawson
Lawson

Reputation: 634

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

Upvotes: 1

Related Questions