Solved

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

Posted on 2006-11-29
10
1,390 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This exercise is about for the following scenario: Dmgr and One node with 2 application server. Each application server contains it owns application. Application server name as follows server1 contains app1 server2 contains app1 Prereq…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now