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

On Apr 10, 4:19 pm, steph <stepha..._at_yahoo.de> wrote:
> On 10 Apr., 15:33, sybrandb <sybra..._at_gmail.com> wrote:
>
> > On Apr 10, 11:30 am, Barry Bulsara <bbulsar..._at_hotmail.com> wrote:
>
> > > On Apr 10, 6:04 am, sybra..._at_hccnet.nl wrote:
>
> > > > On Wed, 9 Apr 2008 15:32:35 -0700 (PDT), 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
>
> > > > Please don't post this unformatted junk.
> > > > Please stop doing things procedurally, inside a loop, where they
> > > > should be done non-procedurally, using SQL
> > > > Please DON'T commit INSIDE a loop.
> > > > Please DON'T use a loop, where you don't NEED one.
> > > > Please learn SQL!
> > > > Please learn PL/SQL!!!
> > > > Please don't post this UNSCALABLE UTTER NONSENSICAL CRAP!
> > > > And if you INSIST posting this CRAP, include a VERSION (4 digits)
>
> > > > Why do you insist using this CRAP, when there are WAY BETTER
> > > > solutions, as I presented to you? WHY? Want to be 'smart', Want to
> > > > INSULT someone, who is trying to learn you something?
>
> > > > Any explanation?
>
> > > > Please return from
>
> > ...
>
> > Erfahren Sie mehr »
>
> is it really necessary to use this explicit language in this forum?
> this has nothing to do with comp.databases.oracle.server anymore! and
> some people wonder, why the number of posts is going down in here ...

Please note I was responding to somone who has insulted me many times before, and who right now, called me 'an antisocial wanker' Regrettably I can't stay polite at such a gross insult. Can you?

--
Sybrand Bakker
Senior Oracle DBA