Leading 0's

DSguru2B · Post by **DSguru2B** » Fri Jul 14, 2006 1:38 pm

use this

FMT(out_fa003_lsmw.PAR_AMOUNT,"R2")

Dont use trim

DS_MJ · Post by **DS_MJ** » Fri Jul 14, 2006 2:00 pm

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.

DSguru2B · Post by **DSguru2B** » Fri Jul 14, 2006 2:11 pm

Actually i just tested both your scenarios. I am getting the results you desire. Did you test it.

DS_MJ · Post by **DS_MJ** » Fri Jul 14, 2006 2:29 pm

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

DS_MJ · Post by **DS_MJ** » Wed Jul 19, 2006 2:09 pm

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

DSguru2B · Post by **DSguru2B** » Wed Jul 19, 2006 2:13 pm

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.

DS_MJ · Post by **DS_MJ** » Wed Jul 19, 2006 3:25 pm

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.

DSguru2B · Post by **DSguru2B** » Wed Jul 19, 2006 3:34 pm

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.

DS_MJ · Post by **DS_MJ** » Wed Jul 19, 2006 3:36 pm

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

DSguru2B · Post by **DSguru2B** » Wed Jul 19, 2006 7:43 pm

What happens when you change the sql type back to decimal and specify the correct length and scale of 2?

DS_MJ · Post by **DS_MJ** » Thu Jul 20, 2006 8:32 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Jul 20, 2006 9:45 am

How about FMT(out_fa003_lsmw.PAR_AMOUNT,"16'0'R2") ?

DS_MJ · Post by **DS_MJ** » Thu Jul 20, 2006 4:35 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Jul 20, 2006 6:15 pm

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.

us1aslam1us · Post by **us1aslam1us** » Thu Jul 20, 2006 6:18 pm

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