Solved

ORA-00979 when creating a view with a correlated subquery

Posted on 2010-11-12
7
1,119 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

740 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