-
Unusual output while using wild cards
Hi all,
I am having an unusual problem with the following queries
Code:
SQL> SELECT count(*) FROM WORKORDER WHERE (crewid like '%US0256662%' and istask = 'N' AND SITEID = 'US02');
COUNT(*)
----------
0
SQL> SELECT count(*) FROM WORKORDER WHERE (crewid like '%US0256662' and istask = 'N' AND SITEID = 'US02');
COUNT(*)
----------
0
SQL> SELECT count(*) FROM WORKORDER WHERE (crewid like 'US0256662%' and istask = 'N' AND SITEID = 'US02');
COUNT(*)
----------
2850
SQL> SELECT count(*) FROM WORKORDER WHERE (crewid ='US0256662' and istask = 'N' AND SITEID = 'US02');
COUNT(*)
----------
2850
My question is why should the first two queries return zero?
Recently the database was migrated from 9207 to 9208 and to a virtualised environment and the data files are now in nfs file systems.Previously, they were in jfs.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Please post results for...
Code:
select count(*)
from dba_users
where username = 'OPS$ORACLE';
select count(*)
from dba_users
where username like '%OPS$ORACLE%';
select count(*)
from dba_users
where username like 'OPS$ORACLE%';
select count(*)
from dba_users
where username like '%OPS$ORACLE';
...if OPS$ORACLE not available use any other account you know is there.
All counts should return: 1
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Here it is:
Code:
SQL> select username from dba_users where username like 'SYSTEM';
USERNAME
------------------------------
SYSTEM
SQL> select username from dba_users where username like '%SYSTEM';
USERNAME
------------------------------
SYSTEM
SQL> select username from dba_users where username like '%SYSTEM%';
USERNAME
------------------------------
SYSTEM
SQL> select username from dba_users where username like 'SYSTEM%';
USERNAME
------------------------------
SYSTEM
BTW: I rebuilt the table and the problem went away.Now all the counts returning the same value.
Still I would be interested to know what caused it.
Recent changes that were made to the database were that it was migrated to a virtual env with nfs file system
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
This is happening again
Code:
SQL> SELECT count(*) FROM WORKORDER WHERE (crewid like '%US0256662%' and istask = 'N' AND SITEID =
'US02');
COUNT(*)
----------
0
SQL> SELECT count(*) FROM WORKORDER WHERE (crewid like '%US0256662' and istask = 'N' AND SITEID = '
US02');
COUNT(*)
----------
0
SQL> SELECT count(*) FROM WORKORDER WHERE (crewid like 'US0256662%' and istask = 'N' AND SITEID = '
US02');
COUNT(*)
----------
2909
SQL> SELECT count(*) FROM WORKORDER WHERE (crewid ='US0256662' and istask = 'N' AND SITEID = 'US02'
);
COUNT(*)
----------
2909
SQL>
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Originally Posted by simply_dba
This is happening again
Time to open a SR with Oracle.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|