CASE in WHERE query

[jeroenw]

Hello,

I created the query below. This works properly.

But I actually want the query to return only the lines that have the value WAAR.

What's the best way to do that? I don't think I can put the name of the column header in the WHERE section.

SELECT
T0.[ItemCode],
T0.[ItemName],
T1.[Price] as 'Inkoopprijs',
T0.[AvgPrice],
CASE WHEN T1.[Price] = T0.[AvgPrice] THEN 'WAAR' ELSE 'ONWAAR' END AS 'Gelijk?'

FROM
OITM T0 INNER JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode]

WHERE
T1.[PriceList] = [%0] AND
T0.[InvntItem] = 'Y' AND
T0.[frozenFor] = 'N'

[inga_babco2]

SELECT
T0.[ItemCode],
T0.[ItemName],
T1.[Price] as 'Inkoopprijs',
T0.[AvgPrice],
CASE WHEN T1.[Price] = T0.[AvgPrice] THEN 'WAAR' ELSE 'ONWAAR' END AS 'Gelijk?'

FROM
OITM T0 INNER JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode]

WHERE
T1.[PriceList] = [%0] AND
T0.[InvntItem] = 'Y' AND
T0.[frozenFor] = 'N'

AND T1.[Price] = T0.[AvgPrice]

[naveen_kolli]

Hi,

Please check this example

SELECT

ItemCode,

ItemName,

Inkoopprijs,

AvgPrice,

Gelijk

FROM (

SELECT

T0.ItemCode,

T0.ItemName,

T1.Price AS Inkoopprijs,

T0.AvgPrice,

CASE WHEN T1.Price = T0.AvgPrice THEN 'WAAR' ELSE 'ONWAAR' END AS Gelijk

FROM OITM T0

INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode

WHERE

T1.PriceList = [%0] AND

T0.InvntItem = 'Y' AND

T0.frozenFor = 'N'

) WHERE Gelijk = 'WAAR';

[Felipe_Lima]

Hi @jeroenw ,

You could just include an additional condition to your WHERE clause:

SELECT
T0.[ItemCode],
T0.[ItemName],
T1.[Price] as 'Inkoopprijs',
T0.[AvgPrice],
CASE WHEN T1.[Price] = T0.[AvgPrice] THEN 'WAAR' ELSE 'ONWAAR' END AS 'Gelijk?'

FROM
OITM T0 INNER JOIN ITM1 T1 ON T0.[ItemCode] = T1.[ItemCode]

WHERE
T1.[PriceList] = [%0] AND
T0.[InvntItem] = 'Y' AND
T0.[frozenFor] = 'N' AND
(SELECT CASE WHEN T1.[Price] = T0.[AvgPrice] THEN 'WAAR' ELSE 'ONWAAR' END AS 'Gelijk?') = 'WAAR'