Strange effects of Cast

#11

Ken schreef:

Quote:

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.

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

It will/might (depending on versions) work if you use:

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

Note how the operation slowly "eats" your column NUM.

Shakespeare

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Strange effects of Cast

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Strange effects of Cast - 02-17-2009 , 02:26 PM