RE: Where clause apparently failing

From: William Wagman <>
Date: Mon, 22 Sep 2008 10:27:10 -0700
Message-ID: <>


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
(530) 754-6208

-----Original Message-----
From: Mark W. Farnham [] Sent: Friday, September 19, 2008 2:32 PM To: William Wagman; Subject: RE: Where clause apparently failing

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!


-----Original Message-----
From: [] On Behalf Of William Wagman
Sent: Friday, September 19, 2008 12:30 PM To:
Subject: Where clause apparently failing


I'm running 64-bit Oracle 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.


Bill Wagman
Univ. of California at Davis
IET Campus Data Center
(530) 754-6208


Received on Mon Sep 22 2008 - 12:27:10 CDT

Original text of this message