Subquery as CASE WHEN condition

Question

Below query, syntax error happens on AS PQ_COUNT

SELECT CASE WHEN
     RESULTS LIKE '%PQ - Duplicate%' AND 
     (SELECT COUNT(*) FROM MY_TABLE WHERE ID = '998877'AND FINAL_RESULTS='FL_57') AS PQ_COUNT >= 1 
    THEN 'PQ count = '|| PQ_COUNT 
ELSE RESULTS END AS RESULTS 

If I moved AS PQ_COUNT inside select query,

(SELECT COUNT(*) AS PQ_COUNT FROM MY_TABLE WHERE ID = '998877'AND FINAL_RESULTS='FL_57') >= 1

the reference of PQ_COUNT in THEN block become invalid identifier (ORA-00904)

What might go wrong here when addressing subquery as CASE WHEN condition?

Answer 1

One option is to use a subquery (or a CTE, as in my example) to calculate number of rows that satisfy condition, and then - as it contains only one row - cross join it to my_table. Something like this:

SQL> WITH
  2     my_table (id, final_results, results) AS
  3     -- sample data
  4        (SELECT '998877', 'FL_57', 'PQ - Duplicate' FROM DUAL),
  5     cnt AS
  6     -- calculate COUNT first ...
  7        (SELECT COUNT (*) pq_count               --> pq_count
  8           FROM MY_TABLE
  9          WHERE     ID = '998877'
 10                AND FINAL_RESULTS = 'FL_57')
 11  -- ... then re-use it in "main" query
 12  SELECT CASE
 13            WHEN     a.results LIKE '%PQ - Duplicate%'
 14                 AND b.pq_count >= 1            --> reused here
 15            THEN
 16               'PQ count = ' || b.PQ_COUNT      --> and here
 17            ELSE
 18               a.results
 19         END AS results
 20    FROM my_table a CROSS JOIN cnt b;

RESULTS
---------------------------------------------------
PQ count = 1

SQL>

Answer 2

You cannot refer to an alias in the same sub-query where you create it; you need to nest sub-queries (or use a sub-query factoring clause; also called a CTE or WITH clause) and refer to it in the outer one:

SELECT CASE
       WHEN results LIKE '%PQ - Duplicate%'
       AND  pq_count >= 1 
       THEN 'PQ count = '|| pq_count
       ELSE results
       END AS RESULTS
FROM   (
  SELECT results,
         ( SELECT COUNT(*)
           FROM   MY_TABLE
           WHERE  ID = '998877'
           AND    FINAL_RESULTS='FL_57'
         ) AS pq_count
  FROM   your_table
);