Oracle: sub select with AS, COALESCE, GROUP BY, HAVING CASE WHEN and ORDER BY

Question

I am typing the below code and it is returning me "ORA-00923: FROM keyword not found where expected". What is happening?

    SELECT
       max(alias.id) AS "id",
       (SELECT alias.name FROM EXAM WHERE alias.id = alias.id ) AS "REF",
       alias.name
   FROM EXAM alias
   GROUP BY alias.name
   ORDER BY alias.name ASC;

Here's a fiddle: http://sqlfiddle.com/#!4/537e2/1

score 1 · Accepted Answer · answered Mar 31 '22 at 21:02

'COLUMN PREFIX' is a scalar string, not a valid (alias) column name. Oracle (flagship, not MySQL) uses Double Quotes for such object names. (The ORA error indicates you are using the flagship product)

Oracle does not distinguish between NULL and ''. anything compared to NULL is unknown. Your case statement will always go to the else clause.

I don't think Oracle allows you to use a column alias in the having or group by clause. I can't test right now.

Oracle: sub select with AS, COALESCE, GROUP BY, HAVING CASE WHEN and ORDER BY

1 Answers1