Re: pl/sql: display employees who hired in specific month

From: chris <lazyboy_2k_at_yahoo.com>
Date: Thu, 10 Apr 2008 08:28:54 -0700 (PDT)
Message-ID: <8805c554-e3d1-42aa-8337-c9d16ef9a186@w4g2000prd.googlegroups.com>


On Apr 10, 6:27 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On Apr 9, 5:32 pm, chris <lazyboy..._at_yahoo.com> wrote:
>
>
>
> > On Apr 9, 11:50 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
> > > Comments embedded.
> > > On Apr 9, 12:42 pm, chris <lazyboy..._at_yahoo.com> wrote:
>
> > > > Hi All,
>
> > > > I'm trying to write a simple script which displays all employees who
> > > > are hired in Sept in 1 output & the others are in a different output.
> > > > So far, when I run my script, all employees (including emp who are
> > > > hired in the month of Sept) are displayed in 'other employees'
> > > > section. It looks like the 1st part of the IF condition doesn't
> > > > work.
>
> > > Of course it works, it simply doesn't work as you expect.
>
> > > > Any helps/suggestions are appreciated.
>
> > > > DECLARE
> > > > CURSOR emp_cursor IS SELECT employee_id, last_name,
> > > > TO_CHAR(hire_date, 'month') "hire_date"
> > > > FROM employees;
> > > > hiredate VARCHAR2(30);
> > > > empno employees.employee_id%TYPE;
> > > > lname employees.last_name%TYPE;
> > > > my_month VARCHAR2(30) := 'september';
>
> > > > BEGIN
> > > > OPEN emp_cursor;
> > > > LOOP
> > > > FETCH emp_cursor INTO empno, lname, hiredate;
> > > > IF hiredate = UPPER(my_month) THEN
> > > > DBMS_OUTPUT.PUT_LINE ('Sep employees: ' || empno || lname
> > > > || hiredate);
> > > > ELSE
> > > > DBMS_OUTPUT.PUT_LINE ('other employees: ' || empno ||
> > > > lname || hiredate);
> > > > END IF;
> > > > EXIT WHEN emp_cursor%NOTFOUND;
> > > > END LOOP;
> > > > CLOSE emp_cursor;
> > > > END;
> > > > /
>
> > > > TIA,
> > > > -Chris
>
> > > Let's take your example, rewrite it slightly to use the EMP table
> > > instead and see why you can't get what you want:
>
> > > SQL> set serveroutput on size 1000000
> > > SQL> --
> > > SQL> -- Your code, using EMP table
> > > SQL> --
> > > SQL> DECLARE
> > > 2 CURSOR emp_cursor IS
> > > 3 SELECT empno, ename,
> > > 4 TO_CHAR(hiredate, 'month') hire_date -- returns lower-case
> > > month
> > > 5 FROM emp;
> > > 6
> > > 7 hiredate VARCHAR2(30);
> > > 8 empno emp.empno%TYPE;
> > > 9 lname emp.ename%TYPE;
> > > 10 my_month VARCHAR2(30) := 'september';
> > > 11
> > > 12
> > > 13 BEGIN
> > > 14 OPEN emp_cursor;
> > > 15 LOOP
> > > 16 FETCH emp_cursor INTO empno, lname, hiredate;
> > > 17 -- comparing lower case to upper case will never match
> > > 18 IF hiredate = UPPER(my_month) THEN
> > > 19 DBMS_OUTPUT.PUT_LINE ('Sep employees: ' || empno || lname ||
> > > hiredate);
> > > 20 ELSE
> > > 21 DBMS_OUTPUT.PUT_LINE ('other employees: ' || empno || lname ||
> > > hiredate);
> > > 22 END IF;
> > > 23 EXIT WHEN emp_cursor%NOTFOUND;
> > > 24 END LOOP;
> > > 25 CLOSE emp_cursor;
> > > 26 END;
> > > 27 /
> > > other employees: 7369SMITHdecember
> > > other employees: 7499ALLENfebruary
> > > other employees: 7521WARDfebruary
> > > other employees: 7566JONESapril
> > > other employees: 7654MARTINseptember
> > > other employees: 7698BLAKEmay
> > > other employees: 7782CLARKjune
> > > other employees: 7788SCOTTdecember
> > > other employees: 7839KINGnovember
> > > other employees: 7844TURNERseptember
> > > other employees: 7876ADAMSjanuary
> > > other employees: 7900JAMESdecember
> > > other employees: 7902FORDdecember
> > > other employees: 7934MILLERjanuary
> > > other employees: 7934MILLERjanuary
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL>
> > > SQL>
> > > SQL> --
> > > SQL> -- Working code, providing the proper results
> > > SQL> --
> > > SQL> DECLARE
> > > 2 CURSOR emp_cursor IS
> > > 3 SELECT empno, ename,
> > > 4 TO_CHAR(hiredate, 'month') hire_date -- returns lower-case
> > > month
> > > 5 FROM emp;
> > > 6
> > > 7 my_month VARCHAR2(30) := 'september';
> > > 8
> > > 9
> > > 10 BEGIN
> > > 11 FOR emprec in emp_cursor LOOP
> > > 12 -- comparing like case
> > > 13 IF emprec.hire_date = my_month THEN
> > > 14 DBMS_OUTPUT.PUT_LINE ('Sep employees: ' || emprec.empno ||'
> > > '|| emprec.ename ||' '|| emprec.hire_date);
> > > 15 ELSE
> > > 16 DBMS_OUTPUT.PUT_LINE ('other employees: ' || emprec.empno
> > > ||' '||emprec.ename ||' '|| emprec.hire_date);
> > > 17 END IF;
> > > 18 END LOOP;
> > > 19 END;
> > > 20 /
> > > other employees: 7369 SMITH december
> > > other employees: 7499 ALLEN february
> > > other employees: 7521 WARD february
> > > other employees: 7566 JONES april
> > > Sep employees: 7654 MARTIN september
> > > other employees: 7698 BLAKE may
> > > other employees: 7782 CLARK june
> > > other employees: 7788 SCOTT december
> > > other employees: 7839 KING november
> > > Sep employees: 7844 TURNER september
> > > other employees: 7876 ADAMS january
> > > other employees: 7900 JAMES december
> > > other employees: 7902 FORD december
> > > other employees: 7934 MILLER january
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL>
>
> > > Attention to detail is important.
>
> > > David Fitzjarrell
>
> > Thanks a lot for all of your helps David & All. Actually, I just use
> > dbms_output pkgs to verify the if condition & I understand where
> > Sybrand is coming from & I'm all appreciating for that. Below is my
> > actual codes & it still has errors errors when I'm running. Do you
> > happen to know where is the errors coming from? THX ALOTS!!!
>
> > CREATE OR REPLACE PROCEDURE raise_sal
> > ( empno IN employees.employee_id%TYPE,
> > percent IN NUMBER)
> > IS
> > BEGIN
> > UPDATE employees
> > SET salary = salary * (1 + percent/100)
> > WHERE employee_id = empno;
> > COMMIT;
> > END raise_sal;
> > /
>
> > -----
>
> > SET SERVEROUTPUT ON
> > SET VERIFY OFF
>
> > DROP TABLE emp_raise;
> > CREATE TABLE emp_raise AS (SELECT * FROM employees WHERE 1 = 2);
> > DROP TABLE emp_no_raise;
> > CREATE TABLE emp_no_raise AS (SELECT * FROM employees WHERE 1 = 2);
>
> > DECLARE
>
> > CURSOR emp_cursor IS
> > SELECT employee_id empno, first_name fname, last_name
> > lname, email email_addr,
> > phone_number phoneno, TO_CHAR(hire_date, 'month')
> > hire_date, job_id jobid, salary sal,
> > commission_pct comm, manager_id mgr_id,
> > department_id deptno
> > FROM employees;
>
> > my_month VARCHAR2(30) := 'september';
>
> > BEGIN
> > FOR emp_rec IN emp_cursor LOOP
> > IF emp_rec.hire_date = my_month THEN
> > raise_sal(empno => emp_rec.empno, percent => 30);
> > INSERT INTO emp_raise (employee_id, first_name,
> > last_name, email, phone_number,
> > hire_date,
> > job_id, salary, commission_pct, manager_id,
>
> > department_id)
> > VALUES (emp_rec.empno, emp_rec.fname,
> > emp_rec.lname, emp_rec.email_addr,
> > emp_rec.phoneno,
> > emp_rec.hire_date, emp_rec.jobid, emp_rec.sal,
> > emp_rec.comm,
> > emp_rec.mgr_id, emp_rec.deptno);
> > ELSE
> > INSERT INTO emp_no_raise (employee_id, first_name,
> > last_name, email, phone_number,
> > hire_date,
> > job_id, salary, commission_pct, manager_id,
>
> > department_id)
> > VALUES (emp_rec.empno, emp_rec.fname,
> > emp_rec.lname, emp_rec.email_addr,
> > emp_rec.phoneno,
> > emp_rec.hire_date, emp_rec.jobid, emp_rec.sal,
> > emp_rec.comm,
> > emp_rec.mgr_id, emp_rec.deptno);
> > END IF;
> > END LOOP;
> > END;
> > /
> > SELECT * FROM emp_raise;
> > SELECT * FROM emp_no_raise;
>
> > DECLARE
> > *
>
> > ERROR at line 1:
> > ORA-01858: a non-numeric character was found where a numeric was
> > expected
> > ORA-06512: at line 22- Hide quoted text -
>
> > - Show quoted text -
>
> The error is produced by the hire_date value; you're trying to insert
> a varchar2 string into a date column and it won't work:
>
> SQL> --
> SQL> -- Create the RAISE_SAL procedure
> SQL> --
> SQL>
> SQL> CREATE OR REPLACE PROCEDURE raise_sal
> 2 ( empno IN employees.employee_id%TYPE,
> 3 percent IN NUMBER)
> 4 IS
> 5 BEGIN
> 6 UPDATE employees
> 7 SET salary = salary * (1 + percent/100)
> 8 WHERE employee_id = empno;
> 9 COMMIT;
> 10 END raise_sal;
> 11 /
>
> Procedure created.
>
> SQL>
> SQL> SET SERVEROUTPUT ON
> SQL> SET VERIFY OFF
> SQL>
> SQL> --
> SQL> -- Prepare the destination tables for input
> SQL> --
> SQL>
> SQL> DROP TABLE emp_raise;
>
> Table dropped.
>
> SQL> CREATE TABLE emp_raise AS (SELECT * FROM employees WHERE 1 = 2);
>
> Table created.
>
> SQL> DROP TABLE emp_no_raise;
>
> Table dropped.
>
> SQL> CREATE TABLE emp_no_raise AS (SELECT * FROM employees WHERE 1 =
> 2);
>
> Table created.
>
> SQL>
> SQL> --
> SQL> -- This code won't work because a date isn't
> SQL> -- supplied for the inserted value
> SQL> --
> SQL>
> SQL> DECLARE
> 2
> 3 CURSOR emp_cursor IS
> 4 SELECT employee_id empno, first_name fname, last_name
> lname, email email_addr,
> 5 phone_number phoneno, TO_CHAR(hire_date, 'month')
> hire_date, job_id jobid, salary sal,
> 6 commission_pct comm, manager_id mgr_id, department_id
> deptno
> 7 FROM employees;
> 8
> 9 my_month
> ...
>
> read more

Thanks a lot David & I really appreciate your helps. After examine the codes, I knew datatype violation came from hire_date, but didn't know how to solve it at the time. Well, I made a little bit of modification on the script & it's working fine now. Thanks. here is what I have done...

..........
CURSOR emp_cursor IS

         SELECT employee_id empno, first_name fname, last_name lname, email email_addr,

                phone_number  phoneno, hire_date  hiredate, job_id
jobid, salary  sal,
                commission_pct  comm, manager_id  mgr_id,
department_id   deptno
         FROM employees;

BEGIN
      FOR emp_rec IN emp_cursor LOOP
             IF TO_CHAR(emp_rec.hiredate, 'month') =  'september' THEN
........

And I use "emp_rec.hiredate" to insert into the tables. Received on Thu Apr 10 2008 - 10:28:54 CDT

Original text of this message