Re: Where clause apparently failing
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: William Wagman <wjwagman@xxxxxxxxxxx>
- Date: Mon, 22 Sep 2008 07:42:13 -0600
Bill,
What a view! I won't even start that discussion...so on to the problem
at hand.
If there are 'funky' results with direct queries on the base tables,
that may eliminate some possible issues. What was the execution plan for
the statement used to access the base table? Were there any operations
in common? That might indicate an issue with the specific operation.
Is the data good? Could it contain 'unprintable' characters? Use the
DUMP function to quickly examine the data returned. You might also want
to perform a block dump to examine the data as it is stored.
Example of DUMP()
SQL> l
1 SELECT study_number,
2 DUMP(study_number),
3 DUMP('UCDCC#128')
4 from ucdv_cc_summ
5* where study_number = 'UCDCC#128'
SQL> /
STUDY_NUMBER
------------------------------------------
DUMP(STUDY_NUMBER)
------------------------------------------
DUMP('UCDCC#128')
----------------------------------------
UCDCC#128
Typ=1 Len=9: 85,67,68,67,67,35,49,50,56
Typ=96 Len=9: 85,67,68,67,67,35,49,50,56
UCDCC#128
Typ=1 Len=9: 85,67,68,67,67,35,49,50,56
Typ=96 Len=9: 85,67,68,67,67,35,49,50,56
Regards,
Daniel Fink
--
Daniel Fink
OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Lost Data? http://www.ora600.be/
William Wagman wrote:
Daniel,
Here is the explain plan, it is miserable and I feel guilty including it but
nevertheless. We are also getting some funky results from querying the base
tables also. Thanks for looking.
William Wagman wrote:
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@xxxxxxxxxxx
(530) 754-6208
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
