Hi Experts,
Please see the below test case . The group by rollup query fails with ORA-00979.
Query 1 : Fails with ORA-00979 , Cause may be due to column alias used in group by clause
Query 2 : Success - Used column alias, but used "|| " operator in select clause
Query 3: Success - Removed extra ( ) from group by clause
Can any one explain why query 1 failed but 2 and 3 worked ?
As per oracle documentation column alias should not be used other than select and order by clause, then why 2nd and 3 query works ?
c_alias
Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause
but not other clauses in the query.
https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BCEJGIBG
SQL>
SQL>
SQL> CREATE TABLE AAA
2 (
3 aaa_id NUMBER,
4 aaa_name VARCHAR2(30),
5 bbb_id NUMBER,
6 CONSTRAINT AAA_PK PRIMARY KEY(aaa_id)
7 );
Table created.
SQL>
SQL> CREATE TABLE BBB
2 (
3 bbb_id NUMBER,
4 bbb_name VARCHAR2(30),
5 bbb_type VARCHAR2(30),
6 ccc_id VARCHAR2(30),
7 CONSTRAINT BBB_PK PRIMARY KEY(bbb_id)
8 );
Table created.
SQL>
SQL> CREATE TABLE CCC
2 (
3 ccc_id VARCHAR2(30),
4 ccc_name VARCHAR2(30),
5 ccc_desc VARCHAR2(240)
6 );
Table created.
Query 1 : Fails - Due to use of column alias in group clause
SQL> SELECT bbb_type,
2 bbb_name,
3 full_name
4 FROM AAA aaa,
5 (SELECT bbb.bbb_id ,
6 bbb.bbb_name ,
7 bbb.bbb_type ,
8 bbb.bbb_name full_name
9 FROM BBB bbb ,
10 CCC ccc
11 WHERE bbb.ccc_id = ccc.ccc_id
12 AND ccc.ccc_name = 'some_value'
13 ) bbb
14 WHERE aaa.bbb_id = bbb.bbb_id(+)
15 GROUP BY ROLLUP ( (bbb_type),(bbb_name,full_name));
full_name
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression
Query 2 : Success - Used column alias in group by clause but mentioned concatention operator ( || with column name)
SQL> SELECT bbb_type,
2 bbb_name,
3 full_name
4 FROM AAA aaa,
5 (SELECT bbb.bbb_id ,
6 bbb.bbb_name ,
7 bbb.bbb_type ,
8 bbb.bbb_name||'' full_name <<< column appended with || operator
9
10 FROM BBB bbb ,
11 CCC ccc
12 WHERE bbb.ccc_id = ccc.ccc_id
13 AND ccc.ccc_name = 'some_value'
14 ) bbb
15 WHERE aaa.bbb_id = bbb.bbb_id(+)
16 GROUP BY ROLLUP ( (bbb_type),(bbb_name,full_name) );
no rows selected
Query 3 : Success - removed extra () from group by rollup
SQL> SELECT
2 bbb_type,bbb_name,full_name
3 FROM AAA aaa,
4 (SELECT
5 bbb.bbb_id
6 ,bbb.bbb_name
7 ,bbb.bbb_type
8 ,bbb.bbb_name full_name
9 FROM BBB bbb
10 ,CCC ccc WHERE bbb.ccc_id = ccc.ccc_id and ccc.ccc_name = 'some_value'
11 ) bbb
12 WHERE aaa.bbb_id = bbb.bbb_id(+)
13 GROUP BY ROLLUP (bbb_type),(bbb_name,full_name);
no rows selected
Thanks,
Sayed