DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Unusual output while using wild cards

  1. #1
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by simply_dba View Post
    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
  •  


Click Here to Expand Forum to Full Width