当CASE语句为true时，SQL返回另一个表的值 [英] SQL to return another table's value when CASE statement is true

问题描述

我想编写一条SQL语句，该语句返回基于具有三个可能结果的CASE语句的值。

I want to write a SQL statement that returns a value based on a CASE statement that has three possible outcomes.

1）如果列值以'C0开头'，它将返回表中两列的连接

1) If the column value begins with 'C0' then it returns a concatenation of two columns in the table

2）如果该列以 L0开头，则将该行中的另一列（_PARENT_PROJECT_NUMBER）中的值并查看该值是否驻留在另一个表中。如果不是，则返回值_PARENT_PROJECT_NUMBER。

2) If the column begins with 'L0' then take the value in another column (_PARENT_PROJECT_NUMBER) in that row and see if that value resides in another table. If not, return the value _PARENT_PROJECT_NUMBER.

3）如果_PARENT_PROJECT_NUMBER确实位于另一个表中，则返回另一个表 Rollup_Project_Mapping中 Rollup_Project列中的值。

3)If _PARENT_PROJECT_NUMBER does reside in the other table, then return the value in the column "Rollup_Project" in the other table, "Rollup_Project_Mapping".

我在下面编写的语句除第12行外均正常运行， Rollup_Project_Mapping。 Rollup_Project 我要在其中返回的位置如果在另一个表中找到了_PARENT_PROJECT_NUMBER，则另一个表的值。错误消息如下：

The statement I've written below works fine except for line 12, "Rollup_Project_Mapping"."Rollup_Project" where I want it to return the other table's value if the _PARENT_PROJECT_NUMBER is found in the other table. Here is the error message:

ERROR:  syntax error at or near "FROM"
LINE 12:      FROM "Rollup_Project_Mapping"."Rollup_Project"

有人知道我如何在此部分返回其他表中的值的CASE语句是否正确？

Does anyone know how I can return the value in the other table when this part of the CASE statement is true?

SELECT
CASE 
  WHEN LEFT("_PROJECT_NUMBER",2)='C0' THEN
    '('||"_PROJECT_NUMBER"||') '||"_PROJECT_DESCRIPTION"
  WHEN LEFT("_PROJECT_NUMBER",2)='L0' THEN
    CASE
      WHEN (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project") IS NOT NULL THEN
        "Rollup_Project_Mapping"."Rollup_Project"
    ELSE "_PARENT_PROJECT_NUMBER"
    END
END AS Rollup_Project2
FROM "PA_ACCOUNTED_TEST";

推荐答案

您只能使用一组何时 / THEN 语句和 EXISTS ：

You can use just a set of WHEN/THEN statements and EXISTS:

SELECT (CASE WHEN LEFT("_PROJECT_NUMBER",2) = 'C0'
             THEN '('||"_PROJECT_NUMBER"||') '||"_PROJECT_DESCRIPTION"
             WHEN LEFT("_PROJECT_NUMBER",2) = 'L0' AND
                  EXISTS (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project")
                  THEN (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project")
             ELSE "_PARENT_PROJECT_NUMBER"
       END) AS Rollup_Project2
FROM "PA_ACCOUNTED_TEST";

这篇关于当CASE语句为true时，SQL返回另一个表的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！