SQL Help Urgent

From: Haris Kusumo <hkusumo_at_socs.uts.EDU.AU>
Date: 1997/05/26
Message-ID: <5masfe$kqr@franklin.socs.uts.EDU.AU>#1/1

Hi,

I have query on production database that using connect by and nvl. For testing I use the following query which is similar to query on production db.
Can you tell me what is wrong with this query?

TEST> list
  1 select empno, ename, sal, hiredate, sysdate from emp   2 connect by prior empno = mgr
  3 and nvl (hiredate, sysdate) >= sysdate   4* start with mgr is null
TEST> /      EMPNO ENAME SAL HIREDATE SYSDATE ---------- ---------- ---------- --------- ---------

      7839 KING 5000 17-NOV-81 26-MAY-97 Can you tell why it returns empno 7839 not empno 7934 which has hiredate equal to NULL?
Isn't that nvl only subtitue the value where hiredate is null only? Is this a bug with sqlplus ?

I appreciated if you could e-mail me the reply to

	hkusumo_at_socs.uts.edu.au or
	haris_kusumo_at_stream.com

Thanks in advance
- Haris -

---

Here is the content of emp table
TEST> select * from emp;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

---

      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30

      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 09-DEC-82       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 12-JAN-83       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30

      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782                 1300
        10

14 rows selected. Received on Mon May 26 1997 - 00:00:00 CDT