Casting Zoned decimal into readable decimal

Tools & Utilities

Casting Zoned decimal into readable decimal


I am migrating a file from a mainframe box to teradata. On the mainframe the field is stored as S9(9)V99. I need to convert it to decimal format on the teradata box. In the S9(9)V99 format the sign is superimposed with the least significant number, and thus converting it to a text format.


-123 would be represented in ZD as 12L

When i use a query to read this field and attempt at casting it into a decimal format it gives me an error(3527) saying "Format string 'S9(9)V99' has combination of numeric , character and graphic values.

The query i am using is:-

select CAST((Field1 (FORMAT 'S9(9)v9(2)')) AS Decimal(11,2))

also tried

select CAST(CAST(Field1 AS FORMAT 'S9(9)v9(2)') AS Decimal(11,2))

Can anyone tell me if there is a valid data type to capture ZD usage.
Teradata Employee

Re: Casting Zoned decimal into readable decimal

The S must be the last format specifier, and you must specify both the format and the new datatype clauses in the same CAST.

SELECT CAST(field1 AS DECIMAL(11,2) FORMAT '9(9)V99S')

Re: Casting Zoned decimal into readable decimal

Also makes sure that your data content in "Field1" and the format specification match accurately or else you might get surprises

for example

'12L' with various conversions

Field1 (DECIMAL(12,2), FORMAT '9(10)v9S') gives -12.30
Field1 (DECIMAL(12,2), FORMAT '9(11)S') gives -123.00


Re: Casting Zoned decimal into readable decimal

Thanks Fred, Joe

it's working good now..

Re: Casting Zoned decimal into readable decimal

Hi Gurus,
As we know we have different ways to Compress the Decimal data Like COMP-3,COMP-4,COMP-5 and COMP

For example if we have COLUMN given as SALARY S9(13)V99 Comp-3 , we create Teradata table as SALARY DECIMAL(15,2)
and LAYOUT as .FIELD SALARY * DECIMAL(15,2) and Mload works fine.
The alternate way is ,we can define the layout as .FIELD SALARY * CHAR(8) (we are computing it as 13+2/2 = 8( rounding 7.5) )
the mention CAST(:SALARY AS DECIMAL(15,2) FORMAT '9(13)V9(2)S') in the DML phase (Insert phase) then Mload works fine.
Problem we are facing is with COMP format .
we dont know the relevent teradata datatype for COMP.I got a COPY BOOK which has a COLUMN as BALANCE S9(13)V99 COMP.

I defined it as BALANCE DECIMAL(15,2) and tried defining the layout in above mentioned 2 ways ,But Mload is failing with error-2679.
I knew the data doesnt has any Bad Chars , it contains 0.00 values.
What is the relevant teradata data type for S9(13)V99 COMP ?
Can you please give me solution for this..?
Hope someone who worked on surely gives me a Answer ..
Thanks In Advance

Re: Casting Zoned decimal into readable decimal

sel cast(field as format '99'')

output should be 12


error--combination of numeric,character and graphic values 

datatype of field float