Reading decimal values in schema files

TonyInFrance · Post by **TonyInFrance** » Thu Jul 31, 2014 2:30 am

Morning / Afternoon / Evening all

I'm going to bore you guys once again with the infamous schema file. Just when I thought I was getting the hang of it, voila, Datastage has just thrown me a curve ball.

I basically have decimal values of 20 significant digits including 9 decimal places which I'm reading with a decimal[20,9] specification. However my actual data contains a thousands' separator which is a space. So 1299.789987789 in my flat file becomes 1 299.789987789

Thus when I read this value I get 1.

Is there any way I could instruct Datastage about the thousands' separator?

Thanks guys

Tony

ray.wurlod · Post by **ray.wurlod** » Thu Jul 31, 2014 2:00 pm

You're in France, using a French locale, which specifies space as the thousands delimiter and comma as the decimal placeholder (which is correct for France). You can change this easily by changing the NLS locale used by that job.

TonyInFrance · Post by **TonyInFrance** » Thu Jul 31, 2014 3:06 pm

Actually in this case the decimal separator is a dot. Don't ask why. Sometimes the French like to behave differently from the norm.

I guess I need to define an NLS locale just to signify a thousands' separator.

TonyInFrance · Post by **TonyInFrance** » Wed Aug 06, 2014 2:31 am

No one?
Craig / Eric ? :p

ray.wurlod · Post by **ray.wurlod** » Wed Aug 06, 2014 3:48 am

You didn't ask another question. I assumed that

TonyInFrance wrote:I guess I need to define an NLS locale just to signify a thousands' separator.

was a statement of intent.

Bonne chance!

chulett · Post by **chulett** » Wed Aug 06, 2014 6:42 am

Same here.

qt_ky · Post by **qt_ky** » Wed Aug 06, 2014 6:44 am

Sorry, I don't know the answer. My passport is expired.

TonyInFrance · Post by **TonyInFrance** » Wed Aug 06, 2014 9:28 am

If you guys mean changing the Default collation locale for stages in the NLS tab for the local job's properties, I just tried that.

I changed it from Project (OFF) to fr_FR

Nothing changed though. The value is still read upto the 1st space.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 06, 2014 6:34 pm

Take a look at what's specified in the fr_FR locale. You may need to create a custom locale definition.

Otherwise read the field as VarChar and use Convert() function to change the delimiter characters.

TonyInFrance · Post by **TonyInFrance** » Thu Aug 07, 2014 1:40 am

I'll try the first solution definitely.

Reading as VarChar and using convert on one field for one file will result in me having to create one particular job for this file, whereas I'm using a generic job to validate 30 files each having its own schema file.