Home » SQL & PL/SQL » SQL & PL/SQL » Group by Error
Group by Error [message #305407] Mon, 10 March 2008 12:43 Go to next message
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 #305411 is a reply to message #305407] Mon, 10 March 2008 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query is correct.
What is your version with 4 decimals?
Are they real tables or views?

Regards
Michel
Re: Group by Error [message #305426 is a reply to message #305411] Mon, 10 March 2008 13:44 Go to previous messageGo to next message
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 #305428 is a reply to message #305426] Mon, 10 March 2008 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does a query on T1 alone works?
Post actual code.

Regards
Michel
Re: Group by Error [message #305440 is a reply to message #305428] Mon, 10 March 2008 15:18 Go to previous messageGo to next message
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 #305446 is a reply to message #305440] Mon, 10 March 2008 15:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe an optimizer problem.
Post explain plan.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel
Re: Group by Error [message #305706 is a reply to message #305407] Tue, 11 March 2008 11:23 Go to previous messageGo to next message
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 #305708 is a reply to message #305706] Tue, 11 March 2008 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please copy and paste as you did it the statement that returns an error.
Then retry the same thing replacing the view name by its definition.

Regards
Michel

[Updated on: Tue, 11 March 2008 11:31]

Report message to a moderator

Re: Group by Error [message #305726 is a reply to message #305407] Tue, 11 March 2008 14:05 Go to previous message
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.
Previous Topic: Autonomous Transaction Error 00164
Next Topic: PL/SQL Variable Declarations
Goto Forum:
  


Current Time: Thu Dec 08 10:07:27 CST 2016

Total time taken to generate the page: 0.09037 seconds