Re: Strange effects of Cast

From: Ken <ken.clough_at_gmail.com>
Date: Tue, 17 Feb 2009 03:19:30 -0800 (PST)
Message-ID: <34fe9815-d8b9-4769-927c-7bf907c7ac09_at_c12g2000yqj.googlegroups.com>

Here is an example of more column interaction with Cast and Nvl including a workaround

CREATE TABLE T1 AS
Select

  Cast('12345678901234567890' As Varchar(12)) As NUM1,
  Cast('abcdefghijklmnopqrst' As Varchar(12)) As CHR1,
  Cast(Null                   As Varchar(12)) As NUL1

From
  Dual;

Select

    Cast(NUM1 As Varchar2(7))            As COL1,
    Cast(NUM1 As Varchar2(9))            As COL2,
    Cast(Nvl(NUL1, CHR1) As Varchar2(4)) As COL3,
    NUM1                                 As COL4

From

   T1;

Select

    Cast(Substr(NUM1, 1, 7)             As Varchar2(7)) As COL1,
    Cast(Substr(NUM1, 1, 9)             As Varchar2(9)) As COL2,
    Cast(Substr(Nvl(NUL1, CHR1), 1, 4)  As Varchar2(4)) As COL3,
    NUM1                                As COL4

From

   T1;

Drop Table T1;

Table created.

Example 1
COL1 COL2 COL3 COL4
------- --------- ---- ------------

1234567 ijkl567 abcd 1234567
1 row selected.

Example 2
COL1 COL2 COL3 COL4
------- --------- ---- ------------

1234567 123456789 abcd 123456789012
1 row selected.

As you can see in Example 1 COL2 has not only the wrong length but also has been partially overwritten by COL3.

Using Substr to size the columns before casting them works around the problem. Still not a good situation to be in. I am wondering what else is being affected.

Ken Received on Tue Feb 17 2009 - 05:19:30 CST