How do I use the result of a CASE statement in a WHERE clause? When I try to run this SELECT statement, I get this error: ORA-00904: "GPASCORE": invalid identifier. How do I use the result of the gpaScore CASE statement in the WHERE clause?

SELECT
  (CASE grade
   WHEN 'A+'
     THEN 4
   WHEN 'A'
     THEN 4
   WHEN 'A-'
     THEN 3.7
   WHEN 'B+'
     THEN 3.3
   WHEN 'B'
     THEN 3
   WHEN 'B-'
     THEN 2.7
   WHEN 'C+'
     THEN 2.3
   WHEN 'C'
     THEN 2
   WHEN 'C-'
     THEN 1.7
   WHEN 'D+'
     THEN 1.3
   WHEN 'D'
     THEN 1
   WHEN 'D-'
     THEN 0.7
   WHEN 'F'
     THEN 0
   ELSE NULL END)   gpaScore
FROM PGFINALGRADES
WHERE gpaScore = 3
The problem is that Oracle evaluates the SELECT after the WHERE clause.

You can either put your query in a subselect:

SELECT gpaScore
FROM   (SELECT ( CASE grade
                   WHEN 'A+' THEN 4
                   WHEN 'A' THEN 4
                   WHEN 'A-' THEN 3.7
                   WHEN 'B+' THEN 3.3
                   WHEN 'B' THEN 3
                   WHEN 'B-' THEN 2.7
                   WHEN 'C+' THEN 2.3
                   WHEN 'C' THEN 2
                   WHEN 'C-' THEN 1.7
                   WHEN 'D+' THEN 1.3
                   WHEN 'D' THEN 1
                   WHEN 'D-' THEN 0.7
                   WHEN 'F' THEN 0
                   ELSE NULL
                 END ) gpaScore
        FROM   PGFINALGRADES) b
WHERE  gpaScore = '3'; 


Or, you could replicate the CASE statement in the WHERE clause:

SELECT ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) gpaScore
FROM   PGFINALGRADES
WHERE ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) = '3';


Or, if your real case is about as simple as this, you can put grade in your WHERE clause:

SELECT ( CASE grade
           WHEN 'A+' THEN 4
           WHEN 'A' THEN 4
           WHEN 'A-' THEN 3.7
           WHEN 'B+' THEN 3.3
           WHEN 'B' THEN 3
           WHEN 'B-' THEN 2.7
           WHEN 'C+' THEN 2.3
           WHEN 'C' THEN 2
           WHEN 'C-' THEN 1.7
           WHEN 'D+' THEN 1.3
           WHEN 'D' THEN 1
           WHEN 'D-' THEN 0.7
           WHEN 'F' THEN 0
           ELSE NULL
         END ) gpaScore
FROM   PGFINALGRADES
WHERE grade = 'B'