Oracle Subselect in NVL (Group By required)

Question

I'm facing a problem in Oracle. I had a SQL where some values were fixed. Now I started replacing them with values from a parameter-table. Some of these fixed values where in a NVL().

Simply said my statement is like this.

SELECT NVL(MAX(t.datefield), to_date('01011900','DDMMYYYY'))
FROM table t;

That works fine.

Now I want to replace the fixed date to a date from my parameter-table with a subselect, which doesn't work at all.

// Works
SELECT NVL(MAX(NULL), 'hello') FROM DUAL;

// Doesn't work
SELECT NVL(MAX(NULL), (SELECT 'hello' FROM DUAL)) FROM DUAL;

The error is:

ORA-00937: .... "not a single-group group function"

I have no idea how to group by a subselect.

Any help is very appreciated! Thanks!

Answer 1

You can't group by a sub-select. However, in order to achieve this your sub-select is only going to be able to return one row. So, change it into a Cartesian join and group by that.

SELECT NVL(MAX(NULL), str) 
  FROM DUAL
 CROSS JOIN ( SELECT 'hello' as str FROM DUAL )
 GROUP BY STR

More generally every column that is not included in an aggregate function must be included in the GROUP BY. Plus NVL() is bad; use COALESCE() or CASE instead.