I want to write a SQL expression that returns a value based on a CASE expression 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 expression 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";