问题

我正在尝试在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 &gt; 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 &gt; j.new_dismiss

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

无法将”case”用作HAVING或WHERE条件中的”new_c”。

问题

答案1

创建用户映射而不使用密码如何配置身份验证

多个订单在添加额外的OR到WHERE条件时变慢。

为什么 JSONB 返回的是字符串？

声明并初始化一个表变量在一条语句中

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。