SQL Query for PL/SQL statement won't work

Question

I am trying to execute an SQL statement that I am planning on using with a PL/SQL cursor down the road. It will fetch an employees name and the projects they are working on if they are working on more than 1 project. For some reason, the "having count(pno)>1" stipulation will not work here. It just says "no data found"

Is there anything I'm doing wrong? I included my DB code below the query.

Query:

select pname, fname
from project, works_on, employee
where pno=pnumber and essn=ssn
group by pname, fname
having count(pno)>1;

Works_on table:

create table works_on (
Essn    char(9) not null,
Pno int not null,
hours   decimal(3,1),
primary key(essn, pno),
foreign key(Essn) references employee,
foreign key(pno) references project);

Project table:

create table project (
Pname varchar2(15) not null,
Pnumber int     not null,
Plocation varchar2(15),
Dnum    int not null,
primary key (Pnumber),
unique (Pname),
foreign key(Dnum) references department(Dnumber));

Employee table:

 create table employee (
 Fname  varchar2(15) not null,
 Minit  char(1),
 Lname  varchar2(15) not null,
 Ssn    char(9),
 Bdate  date,
 Address    varchar2(30),
 Sex    char(1),
 Salary decimal(10,2),
 super_ssn  char(9),
 dno    int,
 primary key (Ssn),
 foreign key (dno) references department(Dnumber));

EDIT

I managed to make this work instead:

select fname, pname
  from employee, works_on, project
  where essn=ssn and pno=pnumber
  group by fname, pname
  having count(pnumber) > 1

What made pnumber work in place of pno?

There is neither a cursor nor PL/SQL in your code - it is entirely SQL. "Is there anything wrong?" How could we possibly tell? Maybe your data is unique on the pno column - in which case returning zero rows would be correct for the HAVING COUNT(pno) > 1 filter. — MT0
– MT0, Commented Apr 23, 2017 at 23:51
My sincere apologies. I edited this to include my tables. Also, this is a regular old SQL statement that will later be included in a PL/SQL cursor. Total oversight by me when writing the question — Gary
– Gary, Commented Apr 23, 2017 at 23:54
I can absolutely edit this to reflect my mistake. Could you remove your downvote if I do this? If of course you were the one to do it. — Gary
– Gary, Commented Apr 23, 2017 at 23:55
@MT0, I seriously feel like an idiot. I was so focused on my problem and what I knew I would later use it for that I posted a very poorly structured question. I hope that my edits have improved it — Gary
– Gary, Commented Apr 23, 2017 at 23:58
The question is still unanswerable since there is no example data which would demonstrate the issue. Try creating a minimal reproducible example with the DML statements necessary to populate the tables as well as your expected output (and the reasoning why that is expected). — MT0
– MT0, Commented Apr 23, 2017 at 23:59

MT0 · Accepted Answer · 2017-04-24 00:22:37Z

1

My expected output is a list of Employee First Names and Project names where the employee is working on more than 1 project.

Something like this:

SELECT e.fname, p.projects
FROM   (
  SELECT w.essn,
         LISTAGG( p.pname, ',' ) WITHIN GROUP ( ORDER BY p.pname ) AS projects
  FROM   works_on w
         INNER JOIN
         project p
         ON ( w.pno = p.pnumber )
  GROUP BY w.essn
  HAVING COUNT( DISTINCT w.pno ) > 1
) p
INNER JOIN
employee e
ON ( p.essn = e.ssn )

or:

SELECT e.fname,
       p.pname
FROM   (
         SELECT w.*,
                COUNT( pno ) OVER ( PARTITION BY essn ) AS num_projects
         FROM   works_on w
       ) w
       INNER JOIN
       employee e
       ON ( e.ssn = w.essn )
       INNER JOIN
       project p
       ON ( w.pno = p.pnumber )
WHERE  w.num_projects > 1

edited Apr 24, 2017 at 0:22

answered Apr 24, 2017 at 0:17

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gary Over a year ago

I managed to make this work instead: select fname, pname from employee, works_on, project where essn=ssn and pno=pnumber; group by fname, pname having count(pnumber)>1;

Collectives™ on Stack Overflow

SQL Query for PL/SQL statement won't work

EDIT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related