Group by Error [message #305407] |
Mon, 10 March 2008 12:43  |
arksjd
Messages: 13 Registered: November 2007
|
Junior Member |
|
|
Hi
When I execute the below query ...It gives a not a group by function ERROR. But I amjust selecting the count of records and not doing any grouping. Kindly let me knwo what could be the reason for this error.
SQL>
SELECT COUNT(*) REC_COUNT
FROM T1
INNER JOIN T2 ON T1.CODE = T2.CODE
SQL> /
INNER JOIN T2 ON T1.CODE = T2.CODE
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression
Thanks
|
|
|
|
Re: Group by Error [message #305426 is a reply to message #305411] |
Mon, 10 March 2008 13:44   |
arksjd
Messages: 13 Registered: November 2007
|
Junior Member |
|
|
Hi Michel
The version is
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
PL/SQL Release 10.2.0.2.0 - Production
In the code T1 is a view which is based on a group by clause.
|
|
|
|
Re: Group by Error [message #305440 is a reply to message #305428] |
Mon, 10 March 2008 15:18   |
arksjd
Messages: 13 Registered: November 2007
|
Junior Member |
|
|
Hi Michel
Below is the Select query
SELECT COUNT(*) REC_COUNT
FROM VW_CASA
INNER JOIN DIM_PRODUCT ON VW_CASA.V_CASA_PROD_CODE = DIM_PRODUCT.V_PROD_CODE
I get an error When I execute the above statement.
ERROR at line 3:
ORA-00979: not a GROUP BY expression
Definition of the view VW_CASA
CREATE OR REPLACE VIEW VW_CASA
AS SELECT v_casa_prod_code,
v_casa_branch_code,
v_casa_ccy_code,
v_casa_int_type_code,
f_casa_dbcr_indicator,
v_casa_int_meth,
SUM(COALESCE(CASE WHEN f_casa_dbcr_indicator = 'C'
THEN (-1) * ABS(n_casa_eop_balance_savings)
ELSE ABS(n_casa_eop_balance_savings)
END,0)) n_casa_eop_balance,
v_casa_bm_ind
FROM VW_STG_CASA
WHERE
COALESCE(VW_STG_CASA.f_casa_closed_ind, 'N') != 'Y'
GROUP BY v_casa_prod_code,
v_casa_branch_code,
v_casa_ccy_code,
v_casa_int_type_code,
v_casa_bm_ind,
f_casa_dbcr_indicator,
v_casa_int_meth
Definition of the view VW_STG_CASA
SELECT * FROM CASA_ACCOUNTS WHERE DATE=CURRENT DATE.
Thanks.
|
|
|
|
Re: Group by Error [message #305706 is a reply to message #305407] |
Tue, 11 March 2008 11:23   |
arksjd
Messages: 13 Registered: November 2007
|
Junior Member |
|
|
Hi Michel
Below is the Explain Plan for the view VW_casa. But when I add a join to the statement, it thorws an error saying " Not a group by expression ".. And I am not able to get the Explain plan when I execute the query with join as it throws an error immediately.
SQL> select count(*) from vw_Casa;
Execution Plan
----------------------------------------------------------
Plan hash value: 347149912
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 22823 (4)| 00:04:34 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | | 5873 | | 22823 (4)| 00:04:34 |
| 3 | HASH GROUP BY | | 5873 | 177K| 22823 (4)| 00:04:34 |
|* 4 | TABLE ACCESS FULL | STG_FCT_CASA_ACCOUNTS | 1854K| 54M| 22632 (3)| 00:04:32 |
| 5 | TABLE ACCESS BY INDEX ROWID| SETUP_MASTER | 1 | 9 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_SETUP_MASTER | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."FIC_MIS_DATE"= (SELECT TO_DATE("V_COMPONENT_VALUE",'yyyymmdd') FROM
REVDEV."SETUP_MASTER" "SETUP_MASTER" WHERE "V_COMPONENT_CODE"='1'))
6 - access("V_COMPONENT_CODE"='1')
SQL>
My apologies for not formatting the code previously.
Thanks.
|
|
|
|
Re: Group by Error [message #305726 is a reply to message #305407] |
Tue, 11 March 2008 14:05  |
arksjd
Messages: 13 Registered: November 2007
|
Junior Member |
|
|
Hi Michel
I have found the solution but I am not able to understand the reason for the same.
I see the problem in base view definition
Below is the Select query
SELECT COUNT(*) REC_COUNT
FROM VW_CASA
INNER JOIN DIM_PRODUCT ON VW_CASA.V_CASA_PROD_CODE = DIM_PRODUCT.V_PROD_CODE
I get an error When I execute the above statement.
ERROR at line 3:
ORA-00979: not a GROUP BY expression
Definition of the view VW_CASA
CREATE OR REPLACE VIEW VW_CASA
AS
SELECT v_casa_prod_code,
v_casa_branch_code,
v_casa_ccy_code,
v_casa_int_type_code,
f_casa_dbcr_indicator,
v_casa_int_meth,
SUM(COALESCE(CASE WHEN f_casa_dbcr_indicator = 'C'
THEN (-1) * ABS(n_casa_eop_balance_savings)
ELSE ABS(n_casa_eop_balance_savings)
END,0)) n_casa_eop_balance,
v_casa_bm_ind
FROM VW_STG_CASA
WHERE
COALESCE(VW_STG_CASA.f_casa_closed_ind, 'N') != 'Y'
GROUP BY v_casa_prod_code,
v_casa_branch_code,
v_casa_ccy_code,
v_casa_int_type_code,
v_casa_bm_ind,
f_casa_dbcr_indicator,
v_casa_int_meth
Definition of the view VW_STG_CASA
SELECT a,
CASE WHEN (v_casa_ccy_code IS NULL OR v_casa_ccy_code = '0') THEN 'MSG'
WHEN (NOT EXISTS ( SELECT * FROM CURRENCY_MASTER B WHERE B.V_CCY_CODE = A.v_casa_ccy_code
AND B.F_LATEST_RECORD_INDICATOR = 'Y' ))
THEN 'OTH' ELSE v_casa_ccy_code END v_casa_ccy_code,
--v_casa_ccy_code
COL_3,
COL_4
FROM CASA_ACCOUNTS WHERE DATE=CURRENT DATE.
If I comment the CASE statement in this view definition , I am not getting the " Group By " error.But if I dont comment then it throws error.
I am surprised Why should this CASE statement cause
"Group By error"
Thanks.
|
|
|