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