RE: Where clause apparently failing

From: William Wagman <wjwagman_at_ucdavis.edu>
Date: Mon, 22 Sep 2008 10:36:20 -0700
Message-ID: <2A8185DC02A8CE4C8413E0A26A8A831A01024DF631@XEDAMAIL2.ex.ad3.ucdavis.edu>


Tim,

Running the query from sqlplus fails also. In that the database was recently upgraded from 9i to 10g and the queries were successful in 9i I set the init parameters

optimizer_mode = rule

optimizer_features_enable = "9.2.0"

And the problem resolved. We know the views are poorly designed the question is now one of narrowing down what has changed. I guess that is the fun part.

Thank you.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
From: Tim Gorman [mailto:tim_at_evdbt.com]
Sent: Friday, September 19, 2008 3:25 PM To: mwf_at_rsiz.com
Cc: William Wagman; oracle-l_at_freelists.org Subject: Re: Where clause apparently failing

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_at_freelists.org<mailto: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<mailto: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<mailto:wjwagman_at_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 Mon Sep 22 2008 - 12:36:20 CDT

Original text of this message