Concatenating zero's for columns.
Posted: Tue Sep 13, 2005 6:52 pm
Hi,
my source system is db2 and target system is teradata. In my source i have a column of numeric data type and in my target db it is charecter data type. But when i run the job i find incomplete data in few records of this column. For Example When a number like 0677 is passed to Teradata it is going into the character column as 677.
So i tried to add zero's by concatenating with the following sql, but i am getting error when i tried to view the data. Here is the sql used,
SELECT Cont_Info,Cust_Name,
(CASE WHEN LENGTH(TRIM(TRAILING'.'FROM TRIM(CAST(NBR AS CHAR(4)))))) =4 THEN TRIM(CAST(NBR AS CHAR(4)))
WHEN LENGTH(TRIM(TRAILING'.'FROM TRIM(CAST(NBR AS CHAR(4))))) =3 THEN '0' || TRIM(TRAILING '.' FROM TRIM(CAST(NBR AS CHAR(4))))
WHEN LENGTH(TRIM(TRAILING'.'FROM TRIM(CAST(NBR AS CHAR(4))))) =2 THEN '00' || TRIM(TRAILING '.' FROM TRIM(CAST(NBR AS CHAR(4))))
WHEN LENGTH(TRIM(TRAILING'.'FROM TRIM(CAST(NBR AS CHAR(4))))) =1 THEN '000' || TRIM(TRAILING '.' FROM TRIM(CAST(NBR AS CHAR(4)))))
FROM CustomerDB;
Please help me where i am going wrong.
Thanks in advance.
my source system is db2 and target system is teradata. In my source i have a column of numeric data type and in my target db it is charecter data type. But when i run the job i find incomplete data in few records of this column. For Example When a number like 0677 is passed to Teradata it is going into the character column as 677.
So i tried to add zero's by concatenating with the following sql, but i am getting error when i tried to view the data. Here is the sql used,
SELECT Cont_Info,Cust_Name,
(CASE WHEN LENGTH(TRIM(TRAILING'.'FROM TRIM(CAST(NBR AS CHAR(4)))))) =4 THEN TRIM(CAST(NBR AS CHAR(4)))
WHEN LENGTH(TRIM(TRAILING'.'FROM TRIM(CAST(NBR AS CHAR(4))))) =3 THEN '0' || TRIM(TRAILING '.' FROM TRIM(CAST(NBR AS CHAR(4))))
WHEN LENGTH(TRIM(TRAILING'.'FROM TRIM(CAST(NBR AS CHAR(4))))) =2 THEN '00' || TRIM(TRAILING '.' FROM TRIM(CAST(NBR AS CHAR(4))))
WHEN LENGTH(TRIM(TRAILING'.'FROM TRIM(CAST(NBR AS CHAR(4))))) =1 THEN '000' || TRIM(TRAILING '.' FROM TRIM(CAST(NBR AS CHAR(4)))))
FROM CustomerDB;
Please help me where i am going wrong.
Thanks in advance.