Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00979
ORA-00979 [message #684250] Wed, 28 April 2021 07:53 Go to next message
a_naq
Messages: 13
Registered: April 2021
Junior Member
Scenario - I have users who are assigned different restrictions over several items. These restrictions are specified in restriction groups. Sometimes it happens that a user is a part of more than one restriction group. Sometimes, by mistake a user is assigned those restriction groups that have a conflict restriction for a common item. For example, User 123 is a part of restriction group A1 and B1 that have a common item Green Ball wherein restriction group A1 has a restriction that User 123 can access only 3 Green Balls a day while restriction group B1 says that User 123 can access only 2 Green Balls a day, thus leading to a conflict. I have to build a query that will fetch the information in such scenarios where there is a conflict. Every user belongs to a particular region, so the results will be filtered using region ID. My query should output.

UserId, Common Item, Restriction Group1 Name, Restriction, Restriction Group2 Name, Restriction
Tables
user - Id, userregionid
userRestriction - userId, restrictionGroup
restrictions- Item, restrictionGroup, restriction, interval // For example, Green Balls, Group A1, 3 , 1 (means 1 day)
My effort:

select user.id, userRestriction.restrictionGroup,restrictions.Item, restriction.restriction, restriction.interval
from user left outer join userRestriction on user.Id = userRestriction.userId
left outer join restrictions on userRestriction.restrictionGroup = restriction.restrictionGroup
group by userRestriction.userid
having count(userRestriction.restrictiongroup)>1 and user.useregionid= '12345'
When I ran this query,I got the following error
ORA- 00979 Not a group By expression

So, I modified my query and added all the selected columns in the groupby clause( although I don't need this)


select user.id, userRestriction.restrictionGroup,restrictions.Item, restriction.restriction, restriction.interval
from user left outer join userRestriction on user.Id = userRestriction.userId
left outer join restrictions on userRestriction.restrictionGroup = restriction.restrictionGroup
group by userRestriction.userid, user.id, userRestriction.restrictionGroup,restrictions.Item, restriction.restriction, restriction.interval,user.useregionid
having count(userRestriction.restrictiongroup)>1 and user.userregionid= '12345'
But this is not fetching anything at all. This should not happen as I have conflict data in my database and it should fetch information.

I doubt if my logic is correct. Is there any other way to write this query ?

Report message to a moderator

Re: ORA-00979 [message #684251 is a reply to message #684250] Wed, 28 April 2021 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Format your query, if you don't know how to do it, learn it using SQL Formatter.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

ORA-00979: not a GROUP BY expression
Something in the SELECT list is neither an aggregate expression nor in the GROUP BY clause.

Report message to a moderator

Re: ORA-00979 [message #684252 is a reply to message #684251] Wed, 28 April 2021 11:09 Go to previous messageGo to next message
a_naq
Messages: 13
Registered: April 2021
Junior Member
Hello,

here is the demo of my problem

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=937538b2feff4c5e21ab4b7156209e7b

Please let me know if anything else is required from my side.

Report message to a moderator

Re: ORA-00979 [message #684253 is a reply to message #684252] Wed, 28 April 2021 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Copy and paste your test case here (don't forget to use tags). We cannot work with an image.
Tell the result you want from the data you give and explain it.

Provide the Oracle version (with 4 decimals).

Report message to a moderator

Re: ORA-00979 [message #684254 is a reply to message #684253] Wed, 28 April 2021 12:13 Go to previous messageGo to next message
a_naq
Messages: 13
Registered: April 2021
Junior Member
Okay, so its Oracle 11g (11.2.0.1)

My tables

1) Employee 
CREATE TABLE employee
  (
     id           VARCHAR(20) PRIMARY KEY,
     userregionid VARCHAR(20)
  )
This is the data that I inserted 

insert into employee values('E-999','12345')
insert into employee values('E-888','12345')
insert into employee values('Y-8323','2233')
Table : Userrestriction

CREATE TABLE userrestriction
  (
     userid           VARCHAR(20),
     restrictiongroup VARCHAR(20)
  )
Values inserted
insert into userrestriction values('E-999','A1')
insert into userrestriction values('E-999','C1')
insert into userrestriction values('E-888','B1')
Table: restrictions
CREATE TABLE restrictions
  (
     item             VARCHAR(20),
     restrictiongroup VARCHAR(20),
     restriction      NUMBER,
     interval         NUMBER
  )
Values inserted

insert into restrictions values('Green Balls', 'A1',1,30)
insert into restrictions values('Green Balls', 'C1',1,1)
insert into restrictions values('Pen', 'C1',1,30)

Scenario - I have users who are assigned different restrictions over several items. These restrictions are specified in restriction groups. Sometimes it happens that a user is a part of more than one restriction group. Sometimes, by mistake a user is assigned those restriction groups that have a conflict restriction for a common item. For example, User 123 is a part of restriction group A1 and B1 that have a common item Green Ball wherein restriction group A1 has a restriction that User 123 can access only 3 Green Balls a day while restriction group B1 says that User 123 can access only 2 Green Balls a day, thus leading to a conflict. I have to build a query that will fetch the information in such scenarios where there is a conflict. Every user belongs to a particular region, so the results will be filtered using region ID. My query should output

UserId, Common Item, Restriction Group Name, Restriction
For example, in the above sample test dataset,the output should be 

E-999 | GreenBalls | A1 | 1 | 30
E-999 | GreenBalls | C1 | 1 | 1

This is the query that I wrote 

SELECT employee.id,
       userrestriction.restrictiongroup,
       restrictions.item,
       restrictions.restriction,
       restrictions.interval
FROM   employee
       left outer join userrestriction
                    ON employee.id = userrestriction.userid
       left outer join restrictions
                    ON userrestriction.restrictiongroup =
                       restrictions.restrictiongroup
                       
                       where employee.userregionid = '12345' 
GROUP  BY userrestriction.userid,
          employee.id,
          userrestriction.restrictiongroup,
          restrictions.item,
          restrictions.restriction,
          restrictions.interval,
          employee.userregionid
HAVING Count(userrestriction.restrictiongroup) > 1
But its fetching blank data.

Please let me know if the problem statement is clear or not. Thank you for your help .

Report message to a moderator

Re: ORA-00979 [message #684255 is a reply to message #684252] Wed, 28 April 2021 12:23 Go to previous message
Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA
Senior Member
 
with t as (
           select  e.id,
                   r.item,
                   ur.restrictiongroup,
                   r.interval,
                   count(distinct r.interval)
                     over(partition by e.id,r.item) collission_indicator
             from  employee e,
                   userrestriction ur,
                   restrictions r
             where e.userregionid = '12345'
               and ur.userid = e.id
               and ur.restrictiongroup = r.restrictiongroup
         )
select  id,
        item,
        restrictiongroup,
        interval
  from  t
  where collission_indicator > 1
  order by id,
           item,
           restrictiongroup,
           interval
/

ID                   ITEM                 RESTRICTIONGROUP       INTERVAL
-------------------- -------------------- -------------------- ----------
E-999                Green Balls          A1                           30
E-999                Green Balls          C1                            1

SQL>
SY.

Report message to a moderator

Previous Topic: pipeline function call in a stored proc and loop
Next Topic: how to change a column to a virtual
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Mar 30 15:23:50 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.