DSXchange

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

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

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

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

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

use this 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...

You will find the BASIC Transformer stage in the Stage Types branch of the Repository. It was intended not to put it on the Palette by default, though you can of course customize your Palette. ... Thank you ray.wurlod and DSguru2B....was able to add the Basic Transformer from the Stage Types using ...

DSguru2B wrote:FMT() us bit available in px
User the basic transformer if you want to use FMT()

Thanks...but Dont have basic Transformer in DataStage Designer edition 7.5.1.A. Viewed the palette and was unable to find it to add it.

Could anyone point out what is wrong with this: (TRIM(FMT(COL_NAME,"R2"),'0','L') Example: 00012345.00 Name SQL TYPE LENGTH SCALE COL_NAME Num 15 2 when I apply TRIM(COL_NAME,'0','L') I get 12345 I want 12345.00 so use FMT() Function to keep the 2 places of the decimal. However, PX is not ...

When you format it to two decimal places it regains its leading zero. 8) ... Hello ray.wurlod: Would really appreciate if you could please tell me how to do this in Parallel job. Cause unable to find anything for format under parjdev.pdf or advpx.pdf. The Basic.pdf mentions the fmt() function but a...

Check out this post http://www.dsxchange.com/viewtopic.php?t=101418 Thanks DS_Guru: I used the TRIM(COL_NAME,'0','L') removes the leading zeros but then it removes the 0's from the decimal place too. Example: 00012345.00 Name SQL TYPE LENGTH SCALE COL_NAME Num 15 2 when I apply TRIM(COL_NAME,'0','L...

skrakesh wrote:Use Round Function: Round(field_name,2)

or

Use longint

I want to remove the leading 0's.

Hi there I have a Numeric value in a job that I am trying to trim just the leading zeros for example:


Field1 [Numeric 17,2] = 000000000000000.00

The result I would like would be 0.00

Any ideas?

Thanks

Oooppps please ignore the above query: Wrote the follwoing query in the filter stage without the 'IN" COL_NAME = 'Y' OR COL_NAME = 'BRZ' OR COL_NAME = 'KC' OR COL_NAME = 'T' OR COL_NAME = 'TK' AND SUBSTRING(ID,1,1) BETWEEN 0 AND 9 AND SUBSTRING(ID,2,1) BETWEEN 'A' AND 'Z' AND NOT SUBSTRING(ID,1...

Looks like it doesn't like IN (in spite of what might be in the manual). Try rewriting the IN clauses as multiple "=" operations joined by OR. ... Thanks ray.wurlod, appreciate it. I broke the query and just tried the following in the Filter stage: COL_NAME = 'Y' OR COL_NAME = 'BRZ' OR CO...