Home » SQL & PL/SQL » SQL & PL/SQL » Subqyery with group by / case when / having (Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production)
Subqyery with group by / case when / having [message #645463] Mon, 07 December 2015 04:58 Go to next message
Kruemel
Messages: 2
Registered: December 2015
Junior Member
Hello everybody,

I am studying for the Oracle SQL Export exam (1Z0-047) with the following book:
OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047) (Oracle Press).

In the chapter on subqueries it is stated that scalar subqueries are not allowed in the following places:
1) GROUP BY clause
2) HAVING clause
3) CASE WHEN
4) CONNECT BY
5) (+ others)

Having worked with Oracle for quite some time, I knew about 1) and 4), however, I was surprised about 2) and 3). So I did a few test queries
and it seems that subqueries in these places work.

The following two examples work:
reg. 2) 
select dep, max(r) from (
select 1 as dep, 1 as r from dual UNION
select 1 as dep, 2 as r from dual UNION
select 2 as dep, 3 as r from dual 
) having max(r) > (select 2 from dual)
group by dep;

reg. 3) 
select case when (select 2 from dual) > 1 then 1 else 0 end from dual;


Do I misinterprete the statement in the book? Or was it maybe not allowed in an Oracle version prior to 10g?


Thanks!

Report message to a moderator

Re: Subqyery with group by / case when / having [message #645464 is a reply to message #645463] Mon, 07 December 2015 05:08 Go to previous messageGo to next message
John Watson
Messages: 8995
Registered: January 2010
Location: Global Village
Senior Member
When you come across an error such as this, the best option is to contact the author through the publisher so that it can be included in the published errata. Include your demonstration, of course.

All authors appreciate this (trust me - as an author myself, I know). We all make mistakes, and no matter how good your technical editor is, some get through.

Incidentally, I am not going to say "good luck with the exam". With your approach to study, you won't need any luck.

Report message to a moderator

Re: Subqyery with group by / case when / having [message #645468 is a reply to message #645464] Mon, 07 December 2015 05:20 Go to previous messageGo to next message
Kruemel
Messages: 2
Registered: December 2015
Junior Member
Thanks, John.

I already thought about contacting the authors, but wanted a second opinion before going that way.

Report message to a moderator

Re: Subqyery with group by / case when / having [message #645476 is a reply to message #645463] Mon, 07 December 2015 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

4) is also wrong (meaningless query, just to show it works):
SQL> select ename, deptno from emp
  2  connect by deptno=(select deptno from dept where loc='NEW YORK') and prior ename < ename
  3  start with deptno=10 and ename='CLARK'
  4  /
ENAME          DEPTNO
---------- ----------
CLARK              10
KING               10
MILLER             10
MILLER             10

Report message to a moderator

Re: Subqyery with group by / case when / having [message #645478 is a reply to message #645476] Mon, 07 December 2015 06:49 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yup, as far as I can think GROUP BY is the only place you can't put a sub-query

Report message to a moderator

Re: Subqyery with group by / case when / having [message #645479 is a reply to message #645478] Mon, 07 December 2015 07:23 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi cookie,

I am able to use subquery in group by also.Please check this:


SELECT    COUNT (*)     AS cnt
FROM       test_tab
GROUP BY  CASE
          WHEN row_id  IN (
                         SELECT   row_id
                         FROM     test_tab
                         WHERE    row_id = '1-2BNEAX'
                    )
          THEN  1
          ELSE  2
       END



Report message to a moderator

Re: Subqyery with group by / case when / having [message #645480 is a reply to message #645479] Mon, 07 December 2015 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Does not work:
SQL> SELECT    COUNT (*)     AS cnt
  2  FROM       test_tab
  3  GROUP BY  CASE
  4            WHEN row_id  IN (
  5                           SELECT   row_id
  6                           FROM     test_tab
  7                           WHERE    row_id = '1-2BNEAX'
  8                      )
  9            THEN  1
 10            ELSE  2
 11         END
 12  /
          WHEN row_id  IN (
               *
ERROR at line 4:
ORA-00904: "ROW_ID": invalid identifier

If you want we test something you must use something we have.

Report message to a moderator

Re: Subqyery with group by / case when / having [message #645481 is a reply to message #645480] Mon, 07 December 2015 07:37 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi Michel,

Please check this:


CREATE TABLE TEST
(
  ROW_ID   VARCHAR2(15 CHAR)                    NOT NULL,
  CREATED  DATE                                 NOT NULL
)


INSERT INTO test
VALUES
( '1-2BNEAX',
  SYSDATE
);

COMMIT;

Then Execute below Query:

SELECT    COUNT (*)     AS cnt
FROM       test
GROUP BY  CASE
          WHEN row_id  IN (
                         SELECT   row_id
                         FROM     test
                         WHERE    row_id = '1-2BNEAX'
                    )
          THEN  1
          ELSE  2
       END;

Report message to a moderator

Re: Subqyery with group by / case when / having [message #645500 is a reply to message #645481] Mon, 07 December 2015 09:43 Go to previous message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
You are confusing things.

GROUP BY  CASE
          WHEN row_id  IN (
                         SELECT   row_id
                         FROM     test
                         WHERE    row_id = '1-2BNEAX'
                    )
          THEN  1
          ELSE  2
       END;


doesn't group by subquery. It groups by CASE result. Compare:

SQL> SELECT    COUNT (*)     AS cnt
  2  FROM       test
  3  GROUP BY  CASE
  4            WHEN row_id  IN (
  5                           SELECT   row_id
  6                           FROM     test
  7                           WHERE    row_id = '1-2BNEAX'
  8                      )
  9            THEN  1
 10            ELSE  2
 11         END;

       CNT
----------
         1

SQL> SELECT    COUNT (*)     AS cnt
  2  FROM       test
  3  GROUP BY  (
  4                          SELECT   row_id
  5                          FROM     test
  6                          WHERE    row_id = '1-2BNEAX'
  7            )
  8  /
                        SELECT   row_id
                        *
ERROR at line 4:
ORA-22818: subquery expressions not allowed here


SQL>


SY.

Report message to a moderator

Previous Topic: Compilation error in Function
Next Topic: Cannot group third column (Querying)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jan 09 09:35:09 CST 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.