Re: SQL Help Urgent

Haris Kusumo wrote:
>
> 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
> (snip data)
> Can you tell why it returns empno 7839 not empno 7934 which has hiredate
> equal to NULL?
> (snip)

Your query correctly identifies emp 7839 (start with mgr is null), but the connect by clause disqualifies the next lower value in the tree (since hiredate < sysdate), so the tree-search never reaches employee 7934. Remember that the connect by clause must apply to all items in the tree.

If you want to qualify individual "leaves" in the tree you must use "where nvl (hiredate, sysdate) >= sysdate"

Hope this helps.

Chrysalis. Received on Mon May 26 1997 - 00:00:00 CDT