Null sub-select resultset

[hazelsisson]

Hello,
I have a query which is similar to this:

select SUM(ENTITLEMENT)
into   timeAllocated
from   bh_entitlements
where  edate between l_start and l_end
and    staff_id = l_user
and    edate not in 
(
	select tdate
	from   bh_time t
	where  (conditions)
)
and edate not in 
(					
	select tdate
	from   lieu_time l
	where  (conditions)
)
and edate not in
(
	select ldate
	from   leave_requests r
	where  (conditions)	
);

The problem is when one of the sub-selects returns 0 rows the whole query doesn't work - I think the result is null.

Is there are any way to get round this? E.g. using nvl/decode, or structuring the query differently? I've tried a few things and they haven't worked so far.

Thanks,
Hazel

 

[lewisp]

Have you tried

select SUM(ENTITLEMENT)
into   timeAllocated
from   bh_entitlements
where  edate between l_start and l_end
and    staff_id = l_user
and    edate not in
[COLOR=red]Nvl([/color](
    select tdate
    from   bh_time t
    where  (conditions)
)[COLOR=red],To_Date('01-JAN-1000','DD-MON-YYYY'))[/color]
and edate not in
[COLOR=red]Nvl([/color](                    
    select tdate
    from   lieu_time l
    where  (conditions)
)[COLOR=red],To_Date('01-JAN-1000','DD-MON-YYYY'))[/color]
and edate not in
[COLOR=red]Nvl([/color](
    select ldate
    from   leave_requests r
    where  (conditions)    
)[COLOR=red],To_Date('01-JAN-1000','DD-MON-YYYY'))[/color];

 

[hazelsisson]

Thanks for your suggestion lewisp.

Unfortunately it didn't work, the syntax was invalid.

I'm now trying the query where the three sub-queries are "unioned" together, I'll post it if I get it working.

Thanks again,
Hazel

 

[sem]

select SUM(ENTITLEMENT)
into   timeAllocated
from   bh_entitlements
where  edate between l_start and l_end
and    staff_id = l_user
and    edate not in 
(
    select tdate
    from   bh_time t
    where  (conditions)
union
    select tdate
    from   lieu_time l
    where  (conditions)
union
    select ldate
    from   leave_requests r
    where  (conditions)    
);

Or use NOT EXISTS construct

Regards, Dima

http://seminihin.narod.ru

 

[hazelsisson]

Thanks for your suggestion Sem. If I had more time I might have got it working but I ended up sticking to the "union" option, similar to this:

select SUM(ENTITLEMENT)
into   timeAllocated
from   bh_entitlements
where  edate between l_start and l_end
and    staff_id = l_user
and    edate not in 
(
    select tdate as e_date
    from   bh_time t
    where  (conditions)
    union
    select tdate as e_date
    from   lieu_time l
    where  (conditions)
    union
    select ldate as e_date
    from   leave_requests r
    where  (conditions)    
    union
    select to_date('01-Jan-1900') as e_date
    from   dual	
);

The last bit is just to make sure that at least one date is returned from the union query.

Thanks again for your suggestions, I'll probably try and use NOT EXISTS in the future.

Hazel