Re: Where clause apparently failing

From: Tim Gorman <tim_at_evdbt.com>
Date: Fri, 19 Sep 2008 16:25:12 -0600
Message-ID: <48D426C8.4070905@evdbt.com>

Only the LIKE and NOT LIKE operators are even aware of wildcard characters.  Operators such as "=", etc treat wildcard characters as data values...

SQL> select count(*) from dual where dummy like '%X%';

  COUNT(*)
----------
         1

SQL> select count(*) from dual where dummy = '%X%';

  COUNT(*)
----------
         0

Mark W. Farnham wrote:

looks weird to me as if # is being taken as some kind of wildcard.

What tool or pathway is submitting the query from what environment? Some
windows access method isn't messing with the "#" is it?

If you urgently need to get the correct answer (as opposed to tracking down
the root cause first, which I'm not saying shouldn't be done eventually),
then I'd try tacking on an

and study_number like '%128'

which should filter down your result set without changing your existing plan
to the worse.

Using an identical tool on a small table built for the exact purpose should
quickly resolve whether this is a view problem. Perhaps create that table as
select with the query that returns your 8693 rows (though possibly just the
one column or maybe one more that is unique so it is easy to track down the
backward looking rows through the view to the source.

Good luck!

mwf

-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]
On Behalf Of William Wagman
Sent: Friday, September 19, 2008 12:30 PM
To: oracle-l@freelists.org
Subject: Where clause apparently failing

Greetings,

I'm running 64-bit Oracle 10.2.0.4.0 EE on Windows server 2003.

The select statement

select patient_id, study_number
from ucdv_cc_summaryae
where study_number = 'UCDCC#128';

returns 8693 rows. Many of these rows have study_number other than ucdcc#128
including ucdcc#157, ucdcc#159, ucdcc#165, ucdcc#171, etc.

ucdv_cc_summaryae is a view and the column study_number is varchar2(100). I
am perplexed and would appreciate any thoughts. I am still having difficulty
getting to Oracle docs online.

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@ucdavis.edu
(530) 754-6208
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l



  

-- http://www.freelists.org/webpage/oracle-l Received on Fri Sep 19 2008 - 17:25:12 CDT