Re: pl/sql: display employees who hired in specific month
Date: Wed, 9 Apr 2008 11:50:03 -0700 (PDT)
Message-ID: <0ae06def-80d0-4cad-b60e-a97a464d60ff@m71g2000hse.googlegroups.com>
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 Received on Wed Apr 09 2008 - 13:50:03 CDT