Page 2 of 3
Posted: Fri Jul 14, 2006 1:38 pm
by DSguru2B
use this
Code: Select all
FMT(out_fa003_lsmw.PAR_AMOUNT,"R2")
Dont use trim
Posted: Fri Jul 14, 2006 2:00 pm
by DS_MJ
DSguru2B wrote:use this
Code: Select all
FMT(out_fa003_lsmw.PAR_AMOUNT,"R2")
Dont use trim
hello DSguru2B. I wish it was as simple....
I need to use Trim cause I need to trim off leading 0's.
I get various formats of data. One of which is:
00000122345.000
and the other as
0000000000.000000
When I use the trim and fmt functions am able to get the desired result
122345.00
however 0000000000.000000 gets trimmed and formated to .00 instead of 0.00.
Posted: Fri Jul 14, 2006 2:11 pm
by DSguru2B
Actually i just tested both your scenarios. I am getting the results you desire. Did you test it.
Posted: Fri Jul 14, 2006 2:29 pm
by DS_MJ
DSguru2B wrote:Actually i just tested both your scenarios. I am getting the results you desire. Did you test it.
Hello DSguru2B:
I just tried it....and it worked.
You are the best......Thanks
Posted: Wed Jul 19, 2006 2:09 pm
by DS_MJ
Further on this:
Column_Name decimal 17
if my source files has values 12345.67 and 00000.00
Doing a straight map to a seq file shows the values as 0000000012345.67
000000000000000.00
Q1. Why does this happen ?
Q2. When I use FMT(COL_NAME,"R2") it replaces the .67 to .00. Need to retain the value .67. How do I accomplish this - dont want to substring ..?
Posted: Wed Jul 19, 2006 2:13 pm
by DSguru2B
Seq. file retains the input as its coming in. Thats why you see it the way its fed into the seq. file.
Are you sure the .67 is changed to .00
That should not happen, you sure your looking at the right row both in input and output.
I dont have access to DataStage so cant test that right now. Someone here will have to confirm that.
Posted: Wed Jul 19, 2006 3:25 pm
by DS_MJ
DSguru2B wrote:Seq. file retains the input as its coming in. Thats why you see it the way its fed into the seq. file.
Are you sure the .67 is changed to .00
That should not happen, you sure your looking at the right row both in input and output.
I dont have access to DataStage so cant test that right now. Someone here will have to confirm that.
Hello DSguru2B:
Am using the Basic transformer and yes am very sure.
Source values:
12235.85
95743.79
-2647.59
48367.00
Do a straight map to a seq file and view data and see it as follows:
000000012235.85
0000095743.79
-000000002647.59
000000048367.00
When I use the fmt function (FMT(COL_NAME,"R2") ) as mentioned above it displays the following:
12235.00
95743.00
-2647.00
48367.00
The only diff is I change the Field type in the Seq File from Decimal to VarChar.
Posted: Wed Jul 19, 2006 3:34 pm
by DSguru2B
And why are you doing that. Any specific reason of changing it to varchar. Change it back to decimal, with length 17 and scale 2. See what the results are.
Posted: Wed Jul 19, 2006 3:36 pm
by DS_MJ
DSguru2B wrote:use this
Code: Select all
FMT(out_fa003_lsmw.PAR_AMOUNT,"R2")
Dont use trim
Hey Thanks for your quick reply....appreciate it.
I am not using Trim function only FMT(DSLink9.OHTOTA,"R2")
Posted: Wed Jul 19, 2006 7:43 pm
by DSguru2B
What happens when you change the sql type back to decimal and specify the correct length and scale of 2?
Posted: Thu Jul 20, 2006 8:32 am
by DS_MJ
DSguru2B wrote:What happens when you change the sql type back to decimal and specify the correct length and scale of 2?
When I keep the SQL type as Decimal and do FMT(out_fa003_lsmw.PAR_AMOUNT,"R2") get the following:
It removes the 2 decimal places and does not format the leading zeros.
So source shows: 12345.67
Get : 00000000000012345
Posted: Thu Jul 20, 2006 9:45 am
by ray.wurlod
How about FMT(out_fa003_lsmw.PAR_AMOUNT,"16'0'R2") ?
Posted: Thu Jul 20, 2006 4:35 pm
by DS_MJ
ray.wurlod wrote:How about FMT(out_fa003_lsmw.PAR_AMOUNT,"16'0'R2") ? ...
Hello ray.wurlod:
When I try FMT(out_fa003_lsmw.PAR_AMOUNT,"16'0'R2") I get 0000000012345.00
Code: Select all
Source TABLE
COL_NAME SQL TYPE LENGTH SCALE NULLABLE
out_fa003_lsmw.PAR_AMOUNT Dec 17 No
TARGET TABLE
COL_NAME SQL TYPE LENGTH SCALE NULLABLE
out_fa003_lsmw.PAR_AMOUNT VarChar 17 No
My source table is DB2 API table. When I view data from it I see the value shown correctly ie: 12345.67. When I straight map it to a seq file it changes its value to 0000000012345 when I keep the SQL TYPE to Decimal in my source table.
When I change my source table SQL TYPE to VarChar and use the FMT function:
FMT(out_fa003_lsmw.FORECAST_AMOUNT,"R2") get 12345.00
FMT(out_fa003_lsmw.PAR_AMOUNT,"16'0'R2") get 0000000012345.00
interesting isint it...????
what do you suggest...?
Thanks
Posted: Thu Jul 20, 2006 6:15 pm
by ray.wurlod
That Fmt works suggests that the decimal place only exists as an implled value in your data. Apply a Trim() function to eliminate the leading zeroes.
I'm still not clear from this thread whether you're using a BASIC Transformer stage or a Transformer stage.
Posted: Thu Jul 20, 2006 6:18 pm
by us1aslam1us
Hi DS_MJ
The problem is with the data coming from your source.The code which DSGURU gave you is perfect. The thing is that you are not defining the scale when you are pulling the data from the source.
Just my suggestion:
Code: Select all
Source TABLE
COL_NAME SQLTYPE LENGTH SCALE NULLABLE
PAR_AMOUNT Dec 17 whatever the scale is No
TARGET TABLE
COL_NAME SQL TYPE LENGTH SCALE NULLABLE
out_fa003_lsmw.PAR_AMOUNT VarChar 17 No
I tried my way just check it once.
Sam