Solved

Error in parsing Query : [ORA-00934: group function is not allowed here ]

Posted on 2006-11-29
10
1,395 Views
Last Modified: 2013-12-01
Trying to write some SQL for a report and I'm getting the error "Error in parsing Query : [ORA-00934: group function is not allowed here ]" when I test it.  For some reason I'm not allowed to use Group By here?  Here is my SQL...

SELECT  EMPL_FIRST_NAME||' '||EMPL_LAST_NAME ,
NVL(TO_CHAR(DEFN_LOAN_AVAILABLE_CREDIT_AMT, '$999,999,999.99'),0) ,
LOAN_COOP_ID, LTYP_DESCRIPTION ,LOAN_ACTUAL_AMT ,LOAN_APPROVED_AMT ,TO_CHAR(LOAN_CLOSED_AMT, '$999,999,999') ,LOAN_REQUESTED_AMT ,(APCT_BUSINESS_NAME||''||APCT_LAST_NAME||','||APCT_FIRST_NAME) ,LOAN_AGRONOMIST
 FROM AIMSREP.APPVIEW,
      AIMSREP.CRNOTEVIEW,
      AIMSREP.EMPVIEW,
      AIMSREP.LAONTYPESVIEW,
      AIMSREP.LOANVIEW,
      ( select max (DEFN_REPORT_DATE) CNDate  from aimsrep.crnoteview)
 WHERE ( (APCT_RECORD_ID = LOAN_APCT_RECORD_ID)
   and (DEFN_LOAN_NO = LOAN_PERMANENT_LOAN_NO)
   and (LTYP_TYPE = LOAN_LTYP_TYPE)
   and (EMPL_ID = LOAN_EMPL_ID_BDO)
   and (CNDATE = DEFN_REPORT_DATE))
   AND (NVL(DEFN_LOAN_AVAILABLE_CREDIT_AMT,0) > 0)
   AND ( ( LOAN_LTYP_TYPE = 'EQ' OR LOAN_LTYP_TYPE = 'FAC' OR LOAN_LTYP_TYPE = 'HOG' OR LOAN_LTYP_TYPE = 'LEAS' OR LOAN_LTYP_TYPE = 'LVST' OR LOAN_LTYP_TYPE = 'MILK' OR LOAN_LTYP_TYPE = 'MISC' OR LOAN_LTYP_TYPE = 'PC' OR LOAN_LTYP_TYPE = 'PMSI' OR LOAN_LTYP_TYPE = 'RE' ) )
   AND (DEFN_REPORT_DATE = CNDATE)
   GROUP BY SUM(DEFN_LOAN_AVAILABLE_CREDIT_AMT)

0
Comment
Question by:MFredin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 6

Expert Comment

by:bpeterse
ID: 18039806
>AIMSREP.LAONTYPESVIEW

Is this just a local typo?
0
 

Author Comment

by:MFredin
ID: 18040016
It was a typo when we created the table.... so for this query it is not a typo....
0
 

Author Comment

by:MFredin
ID: 18040699
bpeterse, any ideas?
0
Save the day with this special offer from ATEN!

Save 30% on the CV211 using promo code EXPERTS30 now through April 30th. The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
LVL 6

Expert Comment

by:bpeterse
ID: 18041003
Since I don't know much sql - yet - this is what I found on Metalink:

Error:  ORA 934  
Text:   group function is not allowed here
-------------------------------------------------------------------------------
Cause:  One of the group functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV,
        or VARIANCE, was used in a WHERE or GROUP BY clause.
Action: Remove the group function from the WHERE or GROUP BY clause.
        The desired result may be achieved by including the function in a
        subquery or HAVING clause.
0
 

Author Comment

by:MFredin
ID: 18041149
Yeah I read the same thing... thats why I posted on this site... because I don't know exactly how to use the Group By to make it work.
0
 
LVL 6

Expert Comment

by:DLyall
ID: 18044259
Try this

SELECT  EMPL_FIRST_NAME||' '||EMPL_LAST_NAME ,
NVL(TO_CHAR(DEFN_LOAN_AVAILABLE_CREDIT_AMT, '$999,999,999.99'),0) ,
LOAN_COOP_ID, LTYP_DESCRIPTION ,LOAN_ACTUAL_AMT ,LOAN_APPROVED_AMT ,TO_CHAR(LOAN_CLOSED_AMT, '$999,999,999') ,LOAN_REQUESTED_AMT ,(APCT_BUSINESS_NAME||''||APCT_LAST_NAME||','||APCT_FIRST_NAME) ,LOAN_AGRONOMIST,
SUM(DEFN_LOAN_AVAILABLE_CREDIT_AMT)
 FROM AIMSREP.APPVIEW,
      AIMSREP.CRNOTEVIEW,
      AIMSREP.EMPVIEW,
      AIMSREP.LAONTYPESVIEW,
      AIMSREP.LOANVIEW,
      ( select max (DEFN_REPORT_DATE) CNDate  from aimsrep.crnoteview)
 WHERE ( (APCT_RECORD_ID = LOAN_APCT_RECORD_ID)
   and (DEFN_LOAN_NO = LOAN_PERMANENT_LOAN_NO)
   and (LTYP_TYPE = LOAN_LTYP_TYPE)
   and (EMPL_ID = LOAN_EMPL_ID_BDO)
   and (CNDATE = DEFN_REPORT_DATE))
   AND (NVL(DEFN_LOAN_AVAILABLE_CREDIT_AMT,0) > 0)
   AND ( ( LOAN_LTYP_TYPE = 'EQ' OR LOAN_LTYP_TYPE = 'FAC' OR LOAN_LTYP_TYPE = 'HOG' OR LOAN_LTYP_TYPE = 'LEAS' OR LOAN_LTYP_TYPE = 'LVST' OR LOAN_LTYP_TYPE = 'MILK' OR LOAN_LTYP_TYPE = 'MISC' OR LOAN_LTYP_TYPE = 'PC' OR LOAN_LTYP_TYPE = 'PMSI' OR LOAN_LTYP_TYPE = 'RE' ) )
   AND (DEFN_REPORT_DATE = CNDATE)
   GROUP BY EMPL_FIRST_NAME||' '||EMPL_LAST_NAME ,
NVL(TO_CHAR(DEFN_LOAN_AVAILABLE_CREDIT_AMT, '$999,999,999.99'),0) ,
LOAN_COOP_ID, LTYP_DESCRIPTION ,LOAN_ACTUAL_AMT ,LOAN_APPROVED_AMT ,TO_CHAR(LOAN_CLOSED_AMT, '$999,999,999') ,LOAN_REQUESTED_AMT ,(APCT_BUSINESS_NAME||''||APCT_LAST_NAME||','||APCT_FIRST_NAME) ,LOAN_AGRONOMIST,
SUM(DEFN_LOAN_AVAILABLE_CREDIT_AMT)
0
 
LVL 12

Expert Comment

by:Gibu George
ID: 18044388
The group by should contain the fileds which are returned i.e

GROUP BY EMPL_FIRST_NAME||' '||EMPL_LAST_NAME ,
NVL(TO_CHAR(DEFN_LOAN_AVAILABLE_CREDIT_AMT, '$999,999,999.99'),0) ,
LOAN_COOP_ID, LTYP_DESCRIPTION ,LOAN_ACTUAL_AMT ,LOAN_APPROVED_AMT ,TO_CHAR(LOAN_CLOSED_AMT, '$999,999,999') ,LOAN_REQUESTED_AMT ,(APCT_BUSINESS_NAME||''||APCT_LAST_NAME||','||APCT_FIRST_NAME) ,LOAN_AGRONOMIST
0
 
LVL 6

Accepted Solution

by:
DLyall earned 250 total points
ID: 18044465
Whoops, should have been

SELECT  EMPL_FIRST_NAME||' '||EMPL_LAST_NAME ,
NVL(TO_CHAR(DEFN_LOAN_AVAILABLE_CREDIT_AMT, '$999,999,999.99'),0) ,
LOAN_COOP_ID, LTYP_DESCRIPTION ,LOAN_ACTUAL_AMT ,LOAN_APPROVED_AMT ,TO_CHAR(LOAN_CLOSED_AMT, '$999,999,999') ,LOAN_REQUESTED_AMT ,(APCT_BUSINESS_NAME||''||APCT_LAST_NAME||','||APCT_FIRST_NAME) ,LOAN_AGRONOMIST,
SUM(DEFN_LOAN_AVAILABLE_CREDIT_AMT)
 FROM AIMSREP.APPVIEW,
      AIMSREP.CRNOTEVIEW,
      AIMSREP.EMPVIEW,
      AIMSREP.LAONTYPESVIEW,
      AIMSREP.LOANVIEW,
      ( select max (DEFN_REPORT_DATE) CNDate  from aimsrep.crnoteview)
 WHERE ( (APCT_RECORD_ID = LOAN_APCT_RECORD_ID)
   and (DEFN_LOAN_NO = LOAN_PERMANENT_LOAN_NO)
   and (LTYP_TYPE = LOAN_LTYP_TYPE)
   and (EMPL_ID = LOAN_EMPL_ID_BDO)
   and (CNDATE = DEFN_REPORT_DATE))
   AND (NVL(DEFN_LOAN_AVAILABLE_CREDIT_AMT,0) > 0)
   AND ( ( LOAN_LTYP_TYPE = 'EQ' OR LOAN_LTYP_TYPE = 'FAC' OR LOAN_LTYP_TYPE = 'HOG' OR LOAN_LTYP_TYPE = 'LEAS' OR LOAN_LTYP_TYPE = 'LVST' OR LOAN_LTYP_TYPE = 'MILK' OR LOAN_LTYP_TYPE = 'MISC' OR LOAN_LTYP_TYPE = 'PC' OR LOAN_LTYP_TYPE = 'PMSI' OR LOAN_LTYP_TYPE = 'RE' ) )
   AND (DEFN_REPORT_DATE = CNDATE)
   GROUP BY EMPL_FIRST_NAME||' '||EMPL_LAST_NAME ,
NVL(TO_CHAR(DEFN_LOAN_AVAILABLE_CREDIT_AMT, '$999,999,999.99'),0) ,
LOAN_COOP_ID, LTYP_DESCRIPTION ,LOAN_ACTUAL_AMT ,LOAN_APPROVED_AMT ,TO_CHAR(LOAN_CLOSED_AMT, '$999,999,999') ,LOAN_REQUESTED_AMT ,(APCT_BUSINESS_NAME||''||APCT_LAST_NAME||','||APCT_FIRST_NAME) ,LOAN_AGRONOMIST
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18100152
"GROUP BY SUM(DEFN_LOAN_AVAILABLE_CREDIT_AMT)"

-This is a wrong syntax; hence the error.

Do you really want to group by this sum?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18100156
You cannot use the aggregate functions inside the group by cluase.

The aggregate functions should be put along with the select list.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Verbose logging is used to diagnose garbage collector problems. By default, -verbose:gc output is written to either native_stderr.log or native_stdout.log.   It is also possible to redirect the logs to a user-specified file. This article will de…
There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question