Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-11-29
10
Medium Priority
?
1,398 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
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!

 
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Configure Web Service (server application) I. Configure security for Web Services methods First, we need to protect Session bean which implements the service: 1. Open EJB deployment descriptor (ejb-jar.xml) in the EJB project that contains you…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

636 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