Forum Stats

  • 3,757,480 Users
  • 2,251,236 Discussions
  • 7,869,840 Comments

Discussions

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

Albert Chao
Albert Chao Member Posts: 60 Green Ribbon

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”

Tagged:

Best Answer

  • KayK
    KayK Member Posts: 1,678 Bronze Crown
    Accepted Answer

    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.

«13

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond

    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
    Albert Chao Member Posts: 60 Green Ribbon

    @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
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond

    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
    Albert Chao Member Posts: 60 Green Ribbon

    @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
    KayK Member Posts: 1,678 Bronze Crown
    edited Sep 2, 2021 1:22PM

    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
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond
    edited Sep 2, 2021 1:46PM

    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
    Albert Chao Member Posts: 60 Green Ribbon

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

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @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
    Albert Chao Member Posts: 60 Green Ribbon

    @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
    KayK Member Posts: 1,678 Bronze Crown
    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.