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.
Concatenating zero's for columns.
Moderators: chulett, rschirm, roy
Why not just use a format function in your transformer.
Something like -
should give you the result you need. :D
Something like -
Code: Select all
Fmt(SourceData.Column,"4'0'R")
Then just add a Trim...
(The 'T' will remove trailling '.')
Code: Select all
Fmt(Trim(SourceData.Col,".", "T"),"4'0'R")
Regards
Chris Fuller
"Reality is what refuses to go away when you stop believing in it"
Chris Fuller
"Reality is what refuses to go away when you stop believing in it"
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
cfuller wrote:Then just add a Trim...
(The 'T' will remove trailling '.')Code: Select all
Fmt(Trim(SourceData.Col,".", "T"),"4'0'R")
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.