Re: Strange effects of Cast

On Feb 16, 11:17 am, Ken <ken.clo...@xxxxxxxxx> wrote:
I am getting unexpected results from a Select statement which uses
Cast to tuncate strings.

In the query below although I get the expected result in COL1 and COL2
COL3 and COL4 return only three characters. In fact if I swap COL1 and
COL2 in the query then all columns are three characters long.

I have not been able to find any references to this problem. Has
anyone else come accross this behavior?

Select
    Cast(NUM1 As Varchar2(7))            As COL1,
    Cast(NUM1 As Varchar2(3))            As COL2,
    Cast(NUM1 As Varchar2(9))            As COL3,
    NUM1                                 As COL4
From
    (
    Select
        '12345678901234567890'  As NUM1
    From
        Dual
    )

COL1    COL COL3      COL4
------- --- --------- --------------------
1234567 123 123       123
1 row selected.

It seems like something similar was reported to the group a year or
two ago. I don't remember what the cause was determined to be, but I
believe that it has to do with the data not residing in an actual
table, so the "before" picture of the generated column is lost after
the first call to CAST.

I am a little suprised that the above experiment did not throw an
error. It appears that you are mis-using the CAST function:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions016..htm

Oracle 10.2.0.4 returns the same results:
SELECT
VERSION
FROM
V$INSTANCE;

VERSION
----------
10.2.0.4.0

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
(
Select
'12345678901234567890' As NUM1
From
Dual
);

COL1 COL COL3 COL4
------- --- --------- ----
1234567 123 123 123

Now, let's try the same experiment with the source data residing in a
table:
CREATE TABLE T1 AS
Select
'12345678901234567890' As NUM1
From
Dual;

COMMIT;

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
T1;

COL1 COL2 COL3
-------------------- -------------------- --------------------
COL4
--------------------
12345678901234567890 12345678901234567890 12345678901234567890
12345678901234567890

The above did not give you the expected results?

Let's look at the table description:
DESC T1

Name Null? Type
-------- -------- --------
NUM1 CHAR(20)

A CHAR column, not a VARCHAR2, was created, let's try another
experiment, this time forcing a VARCHAR2 column to be created in the
table:
CREATE TABLE T2 AS
Select
CAST('12345678901234567890' AS VARCHAR(20)) As NUM1
From
Dual;

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
T2;

COL1 COL2 COL3
-------------------- -------------------- --------------------
COL4
--------------------
12345678901234567890 12345678901234567890 12345678901234567890
12345678901234567890

The above did not give you the expected results, same as before?

Let's try again, this time starting with a numeric value:
Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
(
Select
CAST('12345678901234567890' AS NUMBER) As NUM1
From
Dual
);

ERROR at line 3:
ORA-25137: Data value out of range

So, what is Oracle supposed to do with the last 13 digits of the
number? Maybe it will help if the data is in a table?
CREATE TABLE T3 AS
Select
CAST('12345678901234567890' AS NUMBER) As NUM1
From
Dual;

Select
Cast(NUM1 As Varchar2(7)) As COL1,
Cast(NUM1 As Varchar2(3)) As COL2,
Cast(NUM1 As Varchar2(9)) As COL3,
NUM1 As COL4
From
T3;

ERROR at line 2:
ORA-25137: Data value out of range

Still did not produce the desired result?

Let's try again, this time providing sufficient space for the CAST
call, and then using SUBSTR to pick up only the characters of
interest:
Select
SUBSTR(Cast(NUM1 As Varchar2(20)),1,7) As COL1,
SUBSTR(Cast(NUM1 As Varchar2(20)),1,3) As COL2,
SUBSTR(Cast(NUM1 As Varchar2(20)),1,9) As COL3,
NUM1 As COL4
From
T3;

COL1 COL COL3 COL4
------- --- --------- ----------
1234567 123 123456789 1.2346E+19

I have a feeling that the above is *almost* what you want to see.

COLUMN COL4 99999999999999999999

Select
SUBSTR(Cast(NUM1 As Varchar2(20)),1,7) As COL1,
SUBSTR(Cast(NUM1 As Varchar2(20)),1,3) As COL2,
SUBSTR(Cast(NUM1 As Varchar2(20)),1,9) As COL3,
NUM1 As COL4
From
T3;

COL1 COL COL3 COL4
------- --- --------- ---------------------
1234567 123 123456789 12345678901234567890

The above is probably what you want.

Or, directly from DUAL:
SELECT
SUBSTR(Cast(NUM1 As Varchar2(20)),1,7) As COL1,
SUBSTR(Cast(NUM1 As Varchar2(20)),1,3) As COL2,
SUBSTR(Cast(NUM1 As Varchar2(20)),1,9) As COL3,
NUM1 As COL4
From
(Select
CAST('12345678901234567890' AS NUMBER) As NUM1
From
Dual);

COL1 COL COL3 COL4
------- --- --------- ---------------------
1234567 123 123456789 12345678901234567890

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
.

Relevant Pages