Solved

ORA-00979 when creating a view with a correlated subquery

Posted on 2010-11-12
7
1,124 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

627 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