[SOLVED] ORA-00936: missing expression with CASE WHEN subquery

ORA-00936: missing expression with CASE WHEN subquery

I am getting the error ORA-00936: missing expression with the CASE WHEN statement below. possibly due to the rownum? I have tried LIMIT 1 to no avail and also TOP 1 (but that isn't a thing in Oracle SQL)

Basically, the CASE WHEN below is to catch when the sub query returns more than one value (v_date) and just pick one of the two duplicates.

SELECT DISTINCT
g.v_type AS Type, 
g.f_group AS Group, 
g.v_no AS Number,
g.v_date AS Date, 
g.a_year AS Year, 
CASE WHEN COUNT(SELECT DISTINCT v_date 
                 FROM glv
                 WHERE v_type_reference = g.v_type AND v_no_reference = g.v_no AND g.com = com) = '1' 
     THEN (SELECT DISTINCT v_date 
           FROM glv
           WHERE v_type_reference = g.v_type AND v_no_reference = g.v_no AND g.com = com) 
     ELSE (SELECT v_date 
           FROM glv
           WHERE v_type_reference = g.v_type AND v_no_reference = g.v_no AND g.com = com AND rownum = 1) 
     END AS LLD
FROM glv g
WHERE g.a_year = '2015'

Solution

You have your count and select clauses mixed up; this:

CASE WHEN COUNT(SELECT DISTINCT v_date

should be

CASE WHEN (SELECT COUNT(DISTINCT v_date)

Some of your aliases are reserved words, but I assume you've changed those for posting, as you'd get an ORA-00923 with exactly what is in the question.

As an aside, if your a_year field is a number then '2015' shouldn't be in quotes; similarly in your count comparison, '1' should just be a number, not a string.