Solved

ORA-00979 when creating a view with a correlated subquery

Posted on 2010-11-12
7
1,117 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

839 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