英文:
can't use case as new_c in having or where condition
问题
我正在尝试在PostgreSQL查询中使用CASE，并将其用于where或having条件，并收到以下错误消息：
[Err] ERROR: 列 "new_dismiss" 不存在 第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
英文:
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
答案1
得分: 0
尝试这个 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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论