can't use case as new_c in having or where condition

Question

I'm trying to use CASE in PostgreSQL query and use it in where or having condition and i got this error message

[Err] ERROR: column "new_dismiss" does not exist LINE 14:
trans_requistion_details.original_qty > new_dismiss

SELECT
*,
    CASE
        WHEN dismiss_qty is NULL THEN 0
        ELSE dismiss_qty
    END AS new_dismiss

FROM
 trans_requistion_details
INNER JOIN  sub_ingredients ON  trans_requistion_details.sub_ing_id =  sub_ingredients.sub_ing_id
INNER JOIN  units ON  sub_ingredients.sub_ing_stock_unit =  units.unit_id
INNER JOIN  ing_groups ON  sub_ingredients.ing_group_id =  ing_groups.ing_group_id
 HAVING
 trans_requistion_details.trans_id = 175991 and original_qty > new_dismiss

You need a subquery, CTE, or lateral join. – Gordon Linoff Jan 06 '20 at 13:06 — Gordon Linoff, Jan 06 '20 at 13:06
Use the same case in HAVING. – Cetin Basoz Jan 06 '20 at 13:10 — Cetin Basoz, Jan 06 '20 at 13:10

Jim Jones · Accepted Answer · 2020-01-06T13:50:29.100

0

Try this CTE

WITH j AS (
  SELECT *,
    CASE WHEN dismiss_qty is NULL THEN 0
    ELSE dismiss_qty END AS new_dismiss
FROM trans_requistion_details
) SELECT * FROM j
INNER JOIN  sub_ingredients ON  j.sub_ing_id =  sub_ingredients.sub_ing_id
INNER JOIN  units ON  sub_ingredients.sub_ing_stock_unit =  units.unit_id
INNER JOIN  ing_groups ON  sub_ingredients.ing_group_id =  ing_groups.ing_group_id
WHERE j.trans_id = 175991 and original_qty > j.new_dismiss

edited Jan 06 '20 at 13:50

answered Jan 06 '20 at 13:15

Jim Jones

18,404
3
35
44

1

thnks ,, this code is working good – Tarek Ramadan Jan 06 '20 at 13:48

can't use case as new_c in having or where condition

1 Answers1