Re: uppercase/lowercase

On Tue, 20 Mar 2001 13:57:46 GMT,

        "PasirRis" <jackling_at_singnet.com.sg> wrote:
>
>
> e.g.
> in the emp1 tbl:
> there are 2 records:
> RECORD A: supervisor_id of value = 'A01'
> RECORD C: supervisor_id of value = 'C01'
>
> in the subquery,
> (select u.emp_id from user u, userrole rm
> where u.userid = rm.userid), it returns 2 records:
> RECORD 1: emp_id of value = 'A01'
> RECORD 2: emp_id of value = 'B01'
>
> below is my current version of sql:
>
> select t.supervisor_id
> from emp1 t
> where t.supvisor_id NOT IN
> (select u.emp_id from user u, userrole rm
> where u.userid = rm.userid);
>
> If I run this, no row is returned (but in fact, I am expecting it returns
> RECORD C to me)
>
> If I modify the query a bit, changing it to a IN clause (rather than NOT IN
> clause), the result is logical: it returns RECORD A (because value of RECORD
> A matches RECORD 1)
>
>
> There is no uppercase/lowercase mismatch in my case.

Have you tried using a different approach?

Try something like this:

select t.supervisor_id
from emp1 t
minus
select u.emp_id
from user u, userrole rm
where u.userid = rm.userid

or

select t.supervisor_id
from emp1 t
where not exists (select 'x'

                  from user u, userrole rm
                  where u.userid = rm.userid
                  and u.emp_id = t.supervisor_id)

But I would say that your original query looks fine.

Remco

-- 
qn-195-66-31-144:  02:15:02 up 5 days,  1:39,  8 users,  load average: 2.36, 2.18, 2.01