SQL to return another table's value when CASE statement is true
<p>I want to write a SQL statement that returns a value based on a CASE statement that has three possible outcomes.</p>

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

<p>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.  </p>

<p>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".</p>

<p>The statement I've written below works fine except for line 12, <code>"Rollup_Project_Mapping"."Rollup_Project"</code> 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:</p>

<pre><code>ERROR:  syntax error at or near "FROM"
LINE 12:      FROM "Rollup_Project_Mapping"."Rollup_Project"
</code></pre>

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

<pre><code>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";
</code></pre>