?
Solved

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

Posted on 2006-11-29
10
Medium Priority
?
1,397 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
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 750 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

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…
This article is about some of the basic and important steps to be used to improve the performance in web-sphere commerce application development. 1) Always leverage the Dyna-caching facility provided by the product 2) Remove the unwanted code …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

800 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