Re: Strange effects of Cast

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 16 Feb 2009 12:11:33 -0800 (PST)
Message-ID: <ebbc7bf8-9a38-42b3-8feb-466e2af4d7a4_at_q18g2000vbn.googlegroups.com>

On Feb 16, 11:17 am, Ken <ken.clo..._at_gmail.com> 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. Received on Mon Feb 16 2009 - 14:11:33 CST