Concatenating zero's for columns.

[iwin]

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.

[cfuller]

Why not just use a format function in your transformer.
Something like -

Code: Select all

Fmt(SourceData.Column,"4'0'R")

should give you the result you need. :D

[iwin]

Actually sometimes "." gets added to the target column value. So, i am using this sql.

Thanks for advices

[cfuller]

Then just add a Trim...

Code: Select all

Fmt(Trim(SourceData.Col,".", "T"),"4'0'R")

(The 'T' will remove trailling '.')

[vmcburney]

I don't like seeing data butchered by dodgy sql functions in an ETL tool. This reformatting should be done in a transformer rather then the SQL select to reduce the load on the source database, make the modification transparent to metadata reporting and give you increased control over rejections or exceptions from the formatting functions.

Go with the transformer FMT command. Put it into a routine so you can use the Test function to verify your code is correct and re-use it across jobs.

[iwin]

Hi Burney,
Can you please explain me how to write this in a routine.
Thanks in advance.

[iwin]

Then just add a Trim...

Code: Select all

Fmt(Trim(SourceData.Col,".", "T"),"4'0'R")

(The 'T' will remove trailling '.')

Hi,
I tried this but i get an error :

Area_test..Transformer_2: At row 1, link "DSLink3", while processing column "Ph_cd"
Value "0920" truncated to "092".


Thanks for any advices.

[iwin]

Hi,
Its working now.
Thanks guys.