0

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!

Stix
  • 455
  • 5
  • 16

1 Answers1

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.

Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
  • Excellent- Thanks a million! I don't know why I didn't think that way around. With the Nvl/Coalesce problem you are absolutly right, I was just using the given code. – Stix Sep 08 '14 at 10:15