String to Decimal Issue

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Mohan09
Participant
Posts: 4
Joined: Wed Jul 18, 2018 8:57 pm

String to Decimal Issue

Post by Mohan09 »

Hi,
The source file which i'm receiving has all fields in char format and target table has decimal field. so have used a transformer to convert StringtoDecimal, while doing so i'm getting an error as "expected precision not found"

Input fieldname (EX_SN_REAL - VARCHAR(30))
Target fieldname (EX_SN_REAL - DECIMAL(30,4))

i'm using the below condition:

Code: Select all

If trimleadingtrailing(nulltoempty(trn.EX_SN_REAL)) = 'N.A.' then SetNull()
 Else if trimleadingtrailing(nulltoempty(trn.EX_SN_REAL)) = '' then SetNull()
 Else if IsValidDecimal('Decimal',StringtoDecimal(trn.EX_SN_REAL)) = 0 then 0
  Else StringtoDecimal(trn.EX_SN_REAL)
Since i'm getting the value 'N.A.' and null, so i'm checking both the cases and set null.

The issue is when i have the value like below
1878989999.1212 - its working fine (length is 15), but if i have the input value more than 15 digits its throwing the error.

if its less than or equal to 15 digits, then string to decimal is adding leading zereos and a space to make it 30 bytes like below
0000000000000001878989999.1212
In case of more than 15 digits, this function is throwing error. Can somebody please help me on this one, Thanks..
Regards,
Mohan
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Welcome aboard.

Please check the syntax of IsValid() versus IsValidDecimal().
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply