Re: To_char problem

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 11 Feb 2005 09:17:47 -0800
Message-ID: <1108142100.929284_at_yasure>


Cedric Calentier wrote:

> Version : Oracle 8i Enterprise Edition Release 8.1.7.4.0
> 
> This table contains more tha 37000 records but the problem occurs with
> only 2 records : obj_id0=225936449496832965 and
> obj_id0=226851243171165952.
> Is it possible these 2 records were badly inserted in database
> (conversion or length problem...)??
> 
> I executed this request :
> SQL> select count(*) from invoice_formats_buf_t;
> 
>   COUNT(*)
> ----------
>      37377
> 
> This request indicates there are 37377 records in
> invoice_formats_buf_t table, but in reality there are 37377+2=37379
> records (I'm sure).
> I don't understand why these 2 records are not taken into account by
> the "select count(*)". Does it mean these 2 records are not refered
> into index??
> 
> 
> DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1108092848.503475_at_yasure>...
> 

>>Cedric Calentier wrote:
>>
>>
>>>Hello,
>>>
>>>I've got a probleme when I try to access some data by using "to_char"
>>>function in the "WHERE" clause of my request.
>>>
>>>For example, when I write the following request, it works :
>>>SQL> select ';' || to_char(obj_id0) || ';' from invoice_formats_buf_t
>>>where obj_id0=225936449496832965;
>>>The result is :
>>>';'||TO_CHAR(OBJ_ID0)||';'
>>>------------------------------------------
>>>;225936449496832965;
>>>
>>>But with this one, it doesn't :
>>>SQL> select ';' || to_char(obj_id0) || ';' from invoice_formats_buf_t
>>>where to_char(obj_id0)='225936449496832965';
>>>The result is :
>>>no rows selected
>>>
>>>
>>>For information, the type of the selected field is NUMBER. You can
>>>find here a description of the table:
>>>SQL> desc invoice_formats_buf_t
>>> Name Null? Type
>>> ----------------------------------------- --------
>>>----------------------------
>>> OBJ_ID0 NUMBER
>>> REC_ID NUMBER
>>> BUFFER_BUF BLOB
>>>
>>>Has anyone an idea concerning the origin of this strange behaviour.
>>>
>>>Thanks,
>>>
>>>Cédric Calentier.
>>
>>SQL> create table invoice_formats_buf_t (
>> 2 obj_id0 NUMBER,
>> 3 rec_id NUMBER,
>> 4 buffer_buf BLOB);
>>
>>Table created.
>>
>>SQL> insert into invoice_formats_buf_t
>> 2 (obj_id0)
>> 3 values
>> 4 (225936449496832965);
>>
>>1 row created.
>>
>>SQL> insert into invoice_formats_buf_t
>> 2 (obj_id0)
>> 3 values
>> 4 (999999999999999999);
>>
>>1 row created.
>>
>>SQL> commit;
>>
>>Commit complete.
>>
>>SQL> select ';' || to_char(obj_id0) || ';' from invoice_formats_buf_t
>> 2 where obj_id0=225936449496832965;
>>
>>';'||TO_CHAR(OBJ_ID0)||';'
>>------------------------------------------
>>;225936449496832965;
>>
>>SQL> select ';' || to_char(obj_id0) || ';' from invoice_formats_buf_t
>> 2 where to_char(obj_id0)='225936449496832965';
>>
>>';'||TO_CHAR(OBJ_ID0)||';'
>>------------------------------------------
>>;225936449496832965;
>>
>>SQL>
>>
>>Works just fine in 10.1.0.3. What version do you have?

Please do not top-post.

Try the following:

SELECT LENGTH(obj_id0) FROM invoice_formats_buf_t WHERE obj_id0 IN (your two problem values);

SELECT '-' || TO_CHAR(obj_id0) || '-' FROM invoice_formats_buf_t WHERE obj_id0 IN (your two problem values);

Anything strange?

Also try:
CREATE TABLE AS
SELECT *
FROM invoice_formats_buf_t
WHERE obj_id0 IN (your two problem values);

see what happens in the new table?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Feb 11 2005 - 18:17:47 CET

Original text of this message