Solved

ORA-00979 when creating a view with a correlated subquery

Posted on 2010-11-12
7
1,123 Views
Last Modified: 2013-12-18
When I run this SQL statement I get an ORA-00979: not a GROUP by expression:

CREATE VIEW PS_CG_INCDATRDY_VW (SETID, YC_DATA_CD, YC_DATA_CD_TYP, ASOF_DT, YC_MKT_ISSUE_CD, CG_X_VALUE, CG_Y_VALUE, YC_PRICE, YC_COUPON) AS SELECT A.SETID , A.YC_DATA_CD , 'I' YC_DATA_CD_TYP , C.ASOF_DT ASOF_DT , ' ' YC_MKT_ISSUE_CD , 0 CG_X_VALUE , 0 CG_Y_VALUE , 0 YC_PRICE , 0 YC_COUPON FROM PS_YC_DATADEF_TBL A , PS_YC_TERM_STRUCT B , PS_YC_RATE_TBL C , PS_CG_MKTCURVES_VW D WHERE A.SETID = B.SETID AND A.YC_DATA_CD = B.YC_DATA_CD AND A.YC_DATA_CD_TYP = 'M' AND B.YC_MKT_ISSUE_CD = C.YC_MKT_ISSUE_CD AND A.SETID = D.SETID AND A.YC_DATA_CD = D.YC_DATA_CD AND C.ASOF_DT = D.ASOF_DT AND D.YC_COUNT < ( SELECT COUNT(*) FROM PS_YC_TERM_STRUCT E WHERE B.SETID = E.SETID AND B.YC_DATA_CD = E.YC_DATA_CD ) GROUP BY A.SETID, A.YC_DATA_CD, C.ASOF_DT
Thanks in advance.
0
Comment
Question by:gshanmugam
[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
  • 4
  • 3
7 Comments
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34124220
Try this

CREATE OR REPLACE VIEW PS_CG_INCDATRDY_VW
(
   SETID,
   YC_DATA_CD,
   YC_DATA_CD_TYP,
   ASOF_DT,
   YC_MKT_ISSUE_CD,
   CG_X_VALUE,
   CG_Y_VALUE,
   YC_PRICE,
   YC_COUPON
)
AS
     SELECT A.SETID,
            A.YC_DATA_CD,
            max('I') YC_DATA_CD_TYP,
            C.ASOF_DT ASOF_DT,
            max(' ') YC_MKT_ISSUE_CD,
            max(0) CG_X_VALUE,
            max(0) CG_Y_VALUE,
            max(0) YC_PRICE,
            max(0) YC_COUPON
       FROM PS_YC_DATADEF_TBL A,
            PS_YC_TERM_STRUCT B,
            PS_YC_RATE_TBL C,
            PS_CG_MKTCURVES_VW D
      WHERE     A.SETID = B.SETID
            AND A.YC_DATA_CD = B.YC_DATA_CD
            AND A.YC_DATA_CD_TYP = 'M'
            AND B.YC_MKT_ISSUE_CD = C.YC_MKT_ISSUE_CD
            AND A.SETID = D.SETID
            AND A.YC_DATA_CD = D.YC_DATA_CD
            AND C.ASOF_DT = D.ASOF_DT
            AND D.YC_COUNT <
                  (SELECT COUNT ( * )
                     FROM PS_YC_TERM_STRUCT E
                    WHERE B.SETID = E.SETID AND B.YC_DATA_CD = E.YC_DATA_CD)
   GROUP BY A.SETID, A.YC_DATA_CD, C.ASOF_DT


you cannot use columns names in the select list with out a group function on it except the ones specified in the group by clause
0
 

Author Comment

by:gshanmugam
ID: 34124382
Got the same error, the funny thing is that the SELECT statement runs correctly when I run it by itself, its only when I add the CREATE VIEW clause that I get the error.
0
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34124476
what was the error ..?

try to user create or replace, so that you run the same query multiple times instead of dropping and creating.
0
Technology Partners: 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!

 

Author Comment

by:gshanmugam
ID: 34124534
Sorry, what I meant was when I did executed the CREATE or REPLACE view I get the ORA-00979: not a GROUP by expression. But when I execute the SELECT statement portion the query, it runs just fine without any errors.
0
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34124715
Can you try the same removing this block,

            AND D.YC_COUNT <
                  (SELECT COUNT ( * )
                     FROM PS_YC_TERM_STRUCT E
                    WHERE B.SETID = E.SETID AND B.YC_DATA_CD = E.YC_DATA_CD)

mean while i will try to simulate a similar scenario, to see if i will also get the same error.
0
 

Accepted Solution

by:
gshanmugam earned 0 total points
ID: 34124748
Changing the query around a little bit seems to do the trick instead of this clause in the subquery

WHERE B.SETID = E.SETID AND B.YC_DATA_CD = E.YC_DATA_CD

replaced it with

WHERE A.SETID = E.SETID AND A.YC_DATA_CD = E.YC_DATA_CD

Thank you for your help.


0
 

Author Closing Comment

by:gshanmugam
ID: 34162430
Was able to figure out the solution to the problem once I started interacting with the commenters.
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

Suggested Solutions

Title # Comments Views Activity
best datatype for oracle table email creation 8 108
sum of columns in a row in oracle 3 62
date show only hh:mm 2 50
Sorting a SQL script 5 42
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

752 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