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:
>>
>>
>>>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?
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