Re: Where clause apparently failing

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Mon, 22 Sep 2008 07:42:13 -0600
Message-ID: <48D7A0B5.6080903@optimaldba.com>


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_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 - 08:42:13 CDT

Original text of this message