unexpected result from --where username like '%_ABC' 2

[huchen]

Hello,

I want to get a list of login IDs for all developers from DB1 that do not exist in DB2.

here is the query I am using:

scott@DB1>select username from dba_users
where username like '%_DEV'
and username not in (select username
from dba_users@db2
where username like '%_DEV')
order by username ;

'ELEBEDEV' is in the result list.

But if I run this:
select username from dba_users
where username like '%_DEV';

'ELEBEDEV' is NOT in the result list.

Can anybody help me understand why 'ELEBEDEV' comes from the first query?

Thank you

 

[SantaMufasa]

That is puzzling at this point...How many users are in your dba_users view? If the number is not overwhelming, could you just do a straight query of:

select username from dba_users;

...and see if 'ELEBEDEV' appears in the result set?

BTW, are you aware that the "_" character in your LIKE mask is extraneous since that character represents a single-character wild card (where "%" is an any-number-of-characters wildcard)?

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[huchen]

we have about 20000 users in db1.

scott@DB1>select username from dba_users where username ='ELEBEDEV';
this row exists.

"%_DEV" is developer's login id, your ID will be mufasa_dev in this case.

Please let me know if you need more information.
Thank you.

 

[huchen]

Hello Santa,

I am sorry, I did get "ELEBEDEV" from this query:

scott@DB1>select username from dba_users
where username like '%_DEV' order by username;

Now my question is why '%_' does not work?

 

[carp]

As SantaMufasa indicated, the '_' is being interpreted as a single-character wildcard. So what your query is looking for is any username that has at least one character followed by the sequence 'DEV'. I think what you want is

SELECT username
FROM dba_users
WHERE username LIKE '%\_DEV'
ESCAPE '\';

Also, your original query will probably be more efficient if you use a set operator:

SELECT username FROM dba_users 
   WHERE username like '%_DEV' ESCAPE '\'
MINUS
SELECT username FROM dba_users@db2
   WHERE username LIKE '%\_DEV ESCAPE '\';

 

[huchen]

Thank you Carp, Thank you Santa. Wish you have a wonderful new year.