|
|
|
|
|
|
| Author |
Message |
Cedric Calentier
*nix forums beginner
Joined: 10 Feb 2005
Posts: 2
|
 Posted: Thu Feb 10, 2005 3:50 pm Post subject:
To_char problem
|
|
|
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. |
|
| Back to top |
|
 |
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
| Posted: Fri Feb 11, 2005 2:36 am Post subject:
Re: To_char problem
|
|
|
Cedric Calentier wrote:
| Quote: | 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?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
|
Cedric Calentier
*nix forums beginner
Joined: 10 Feb 2005
Posts: 2
|
| Posted: Fri Feb 11, 2005 8:15 am Post subject:
Re: To_char problem
|
|
|
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@x.washington.edu> wrote in message news:<1108092848.503475@yasure>...
| Quote: | 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? |
|
|
| Back to top |
|
|
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
| Posted: Fri Feb 11, 2005 4:17 pm Post subject:
Re: To_char problem
|
|
|
Cedric Calentier wrote:
| Quote: | 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@x.washington.edu> wrote in message news:<1108092848.503475@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@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Wed Jan 07, 2009 6:02 pm | All times are GMT
|
|
Industrial Directory | Car salvage | Loans | Carrinhos para bebê | Mobile Phone
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
»
Tools
