CODEWITHSUNDEEP
sqlpostgresql
jones-chris
jones-chris

Reputation: 691

SQL to return another table's value when CASE statement is true

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

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

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)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".

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"

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";

Upvotes: 2

Views: 3275

Answers (2)

jones-chris
jones-chris

Reputation: 691

I adapted the original SQL statement to work based on @Gordon Linoff answer in case anyone wanted it as another option:

SELECT
CASE 
  WHEN LEFT("_PROJECT_NUMBER",2)='C0' THEN
    '('||"_PROJECT_NUMBER"||') '||"_PROJECT_DESCRIPTION"
  WHEN LEFT("_PROJECT_NUMBER",2)='L0' AND (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project") IS NOT NULL 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";

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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";

Upvotes: 5

Related Questions