Need help with the below assignment. You can take any table for reference

Albert Chao

Create a user defined exception named “DUPLICATE_EMPLOYEE_JOBS’ . The above exception has to be raised provided if there are any duplicates available for empJob column in the table named “Employee”, handle the same with an error message “More Than one Employee for the same Job”

KayK

Maybe something like this

create or replace procedure check_job ( job_id_in varchar2 )
is
 lv_count number(10);
 lv_emp  employees.last_name%TYPE;
 DUPLICATE_EMPLOYEE_JOBS EXCEPTION; 
BEGIN 
 select count(job_id), max(LAST_NAME) 
   into lv_count, lv_emp
   from employees 
  where job_id = job_id_in
  group by job_id ;
--
 IF lv_count >1 THEN 
   RAISE DUPLICATE_EMPLOYEE_JOBS;
 else
   DBMS_OUTPUT.PUT_LINE('The one and only employee of ' || job_id_in || ' is ' || lv_emp ); 
 END IF; 
--
EXCEPTION 
 WHEN DUPLICATE_EMPLOYEE_JOBS THEN 
  DBMS_OUTPUT.PUT_LINE('MORE THAN ONE EMPLOYEE FOR THE SAME JOB'); 
END;    
/
< SCOTT:op57 > exec check_job ( 'AC_MGR' )
The one and only employee of AC_MGR is Higgins

< SCOTT:op57 > exec check_job ( 'SH_CLERK' )
MORE THAN ONE EMPLOYEE FOR THE SAME JOB

The other question is: do you really need an exception ? Or will it work a function and a defined return value.

Frank Kulash

Hi, @User_3GK1M

A lot of people are willing to help you. Not that many are willing to do all your work for you.

What have you tried already? What was the problem with it?

Whenever you have any question, post a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that data, and why. In this case, you might wnat to post something like "When I have on these two rows: INSERT ..., then the PL/SQL should run without raising the exception because ..., but if I add this row: INSERT ...,, then the exception should be raised, because ..."

Albert Chao

@Frank Kulash Thanks for the info. Below things I have tried.

select job_id, count(job_id) from hr.employees group by job_id having count(job_id)>1;

Basically, this is the query that is giving me the counts of duplicates columns. SO, I am confused how to write this in IF condition.

DECLARE 

  DUPLICATE_EMPLOYEE_JOBS EXCEPTION; 

BEGIN 

  IF condition THEN 

   RAISE DUPLICATE_EMPLOYEE_JOBS; 

  END IF; 

EXCEPTION 

  WHEN DUPLICATE_EMPLOYEE_JOBS THEN 

  DBMS_OUTPUT.PUT_LINE('EMPLOYEE HAS MORE THAN ONE JOB ROLE); 

END; 

Frank Kulash

Hi,

That's a good start. Where you have the word condition in

 IF condition THEN 

you need to put an actual condition, for example:

IF max_rows_per_empjob > 1 THEN

To compute max_rows_per_empjob, you can use SELECT ... INTO ... GROUP BY.

Don't forget the single-quote needed at the end of string literals.

DBMS_OUTPUT.PUT_LINE ('EMPLOYEE HAS MORE THAN ONE JOB ROLE'); 

Albert Chao

@Frank Kulash How to compute max_rows_per_job ?? Should I write this query select job_id, count(job_id) from hr.employees group by job_id having count(job_id)>1;

KayK

Hi 3G,

you write above this query:

select job_id, count(job_id) from hr.employees group by job_id having count(job_id)>1;

and this message

DBMS_OUTPUT.PUT_LINE('EMPLOYEE HAS MORE THAN ONE JOB ROLE); 

I don't really familiar with the hr schema, but i think your query gives back all the jobs with more than one employee. And this doesn't match the output from the exception.

Please check it with something like this

select job_id, count(job_id), max(EMPLOYEE_ID), min(EMPLOYEE_ID) from ...

regards

Kay

Frank Kulash

Hi, @User_3GK1M

@Frank Kulash How to compute max_rows_per_job ?? Should I write this query select job_id, count(job_id) from hr.employees group by job_id having count(job_id)>1;

That depends on your data and your requirements. Originally, you said you had a table called employee with a column called empjob, but the query above uses a table called employees with a column called job_id. Once again, post a little sample data (CREATE TABLE and INSERT statements), the results you want from that sample data, and an explanation of how you get those results from that data. If you can use commonly available tables (such as hr.employees) then you don't have to post the sample data, just the results and explanation.

The query you posted:

select    job_id
,         count (job_id) 
from      hr.employees 
group by  job_id 
having    count (job_id) > 1;

is a step in the right direction. It produces a result set with one row for every job_id that occurs more than once. If I understand the problem correctly (which is uncertain, absent any sample data, results and explanation) what you really want to know is whether that query produces any results or not. How can you tell how many rows are in the result set of the query above? One way is to use sub-query; another is to use nested aggregate functions.

Albert Chao

@KayK Thanks but where should I write this query??

Albert Chao

@Frank Kulash The requirement is like suppose I have one table say Employee with attributes like empno, empname, job

Empno Empname Job

1 Frank IT

2 Vinay HR

3 Joe IT

So, there are 2 job with the 'IT' so if I select based on the Job then it will give me count as 2. So, my code should throw an exception saying EMPLOYEE HAS MORE THAN ONE JOB ROLE

Albert Chao

@Frank Kulash This is my sample code which currently I am writing.

set serveroutput on;

DECLARE 

lv_count number(10);

  DUPLICATE_EMPLOYEE_JOBS EXCEPTION; 

BEGIN 

select count(1) into lv_count from hr.employees group by job_id having count(job_id)>1;

  IF lv_count > 1 THEN 

   RAISE DUPLICATE_EMPLOYEE_JOBS; 

  END IF; 

EXCEPTION 

  WHEN DUPLICATE_EMPLOYEE_JOBS THEN 

  DBMS_OUTPUT.PUT_LINE('EMPLOYEE HAS MORE THAN ONE JOB ROLE'); 

END; 

Please suggest

KayK

Empno Empname Job
1     Frank   IT
2     Vinay   HR
3     Joe     IT

So, there are 2 job with the 'IT' so if I select based on the Job then it will give me count as 2. So, my code should throw an exception saying EMPLOYEE HAS MORE THAN ONE JOB ROLE

I would say the employees Frank and Joe have the same job. If they are able to have more than one job you have a Many-To-Many Relationship and you need an intersection table to realise this.