All Forums Tools
jjob 2 posts Joined 01/08
15 Jan 2008
Casting Zoned decimal into readable decimal

Hi,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.Eg:- -123 would be represented in ZD as 12LWhen 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 triedselect 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.

Fred 1096 posts Joined 08/04
15 Jan 2008

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')

joedsilva 505 posts Joined 07/05
15 Jan 2008

Also makes sure that your data content in "Field1" and the format specification match accurately or else you might get surprisesfor example'12L' with various conversionsField1 (DECIMAL(12,2), FORMAT '9(10)v9S') gives -12.30Field1 (DECIMAL(12,2), FORMAT '9(11)S') gives -123.00

jjob 2 posts Joined 01/08
16 Jan 2008

Thanks Fred, Joeit's working good now..

Satish Mee 10 posts Joined 04/05
29 Jan 2008

Hi Gurus,As we know we have different ways to Compress the Decimal data Like COMP-3,COMP-4,COMP-5 and COMPFor 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

raj786 23 posts Joined 04/14
04 Mar 2016

sel cast(field as format '99'')

output should be 12


error--combination of numeric,character and graphic values 

datatype of field float







You must sign in to leave a comment.