RE: Where clause apparently failing

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 19 Sep 2008 17:31:34 -0400
Message-ID: <69EB9203B76E4C5E9F65A1803EF1D572@rsiz.com>


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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman
Sent: Friday, September 19, 2008 12:30 PM To: oracle-l_at_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_at_ucdavis.edu
(530) 754-6208
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 19 2008 - 16:31:34 CDT

Original text of this message