Why does the SQL work differently?

meenakshis
meenakshis used Ask the Experts™
on
Hi,

  Does any one know, why the below mentioned SQL does not work in specified Syntax, but works fine if mentioned in a different way.

   I wanted to know the tables which have been cached in the Database, the SQL written is :-
 
   SELECT owner,table_name,cache
   FROM   all_tables
   WHERE  owner NOT IN ('SYS','SYSTEM')
   AND    cache NOT LIKE 'N%';

   even tried changing the AND clause with
   AND   cache = 'Y';

This SQL works fine, if the AND clause is changed as follows :-

   AND cache NOT LIKE '%N';

Please reply..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
it works
SELECT owner,table_name,cache
  FROM   all_tables
  WHERE  owner NOT IN ('SYS','SYSTEM')
  AND    cache NOT LIKE '%N';

I tested

Author

Commented:
I know it works with the last AND clause mentioned. But the field "cache" defined in table all_tables is VARCHAR2(5) and the values which can be inserted in this column is either 'Y' or 'N'.

Since it is a Varchar2 field, then the Query should work with 'N%' and NOT '%N'.

You can test the same with some dummy table having the field with Varchar2(5) column and assign value 'N' to it and see the query for it's display will work as 'N%' and not '%N'?

Using Oracle Database : 9.2.0.1.0
SQL Plus : 8.0.6.0.0

Commented:
all_tables is a view not table and cache column is one of
'    N',
'    Y',
'    ?'
Commented:
First, if you look at the data in all_tables.cache, it appears that Oracle is writing this field with Left-Padded spaces, so it would be either '    N' or '    Y', which is oppposite normal behavior if you wrote 'N' or 'Y' to it, as then it would appear in the table in SQL*Plus as 'N    ' or 'Y    ', but being VARCHAR2, would return as 'N' or 'Y'.

Chaulk it up to an Oracle internal quirk and just roll with it.

Use LIKE '%N%' and it should work all the time.

Example:

  1* select count(*) from all_tables where cache like '%N%'
DEV>/

  COUNT(*)
----------
      1138

DEV>ed
Wrote file afiedt.buf

  1* select count(*) from all_tables where cache like '%N'
DEV>/

  COUNT(*)
----------
      1138

DEV>ed
Wrote file afiedt.buf

  1* select count(*) from all_tables where cache like 'N%'
DEV>/

  COUNT(*)
----------
         0
Helena Markováprogrammer-analyst

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept markag's comment as answer.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial