Hi,
Im having some problems with the dataformating of some decimal fields.
Id like to make a field with a total length of 12, with 6 significant
digits.
ie. 12345.123456
How should this be done in datastage ?
This is what I did, and the result from different data values.
SQL Type: Decimal
Length: 12
Scale: 6
Input Output
123456789012345.123456789 123456789012345 // decimals are gone
1234567890.123456789 1234567890.12346 // decimal is rounded
(1 lost)
12345.123456789 12345.123457 // decimal is rounded
123456789012.123456789 123456789012.123 // 12.6 ??????
Hope someone can help.
br
Tom
--------------------------------------------------------
Tom P. Nielsen
Project Masterdata
Reg.nr. 4853 / HH55.1.3
Nordea Bank Denmark
Phone: +45 33335223 / Plus nr. 435223
SQL types - formating fails
Try the following, where X is the input value.
Field(X,".",1,1)[5]:".":Fmt(Field(X,".",2,1)[1,6],"L%6")
Field(X,".",1,1) returns whatever precedes the ".".
The [5] returns the rightmost five characters.
Field(X,".",2,1) returns the 2nd "." delimited field.
[1,6] returns the first six characters.
Fmt(k,"L%6") returns k left justified on a background of six zeroes.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Thu, 28 Nov 2002 10:17:36 +0100
To: datastage-users@oliver.com
Subject: SQL types - formating fails
> Hi,
>
> Im having some problems with the dataformating of some decimal fields.
>
> Id like to make a field with a total length of 12, with 6 significant
> digits.
> ie. 12345.123456
>
> How should this be done in datastage ?
> This is what I did, and the result from different data values.
>
>
>
> SQL Type: Decimal
> Length: 12
> Scale: 6
>
> Input Output
> 123456789012345.123456789 123456789012345 // decimals are gone
> 1234567890.123456789 1234567890.12346 // decimal is rounded
> (1 lost)
> 12345.123456789 12345.123457 // decimal is rounded
> 123456789012.123456789 123456789012.123 // 12.6 ??????
>
> Hope someone can help.
> br
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
>
>
>
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
Field(X,".",1,1)[5]:".":Fmt(Field(X,".",2,1)[1,6],"L%6")
Field(X,".",1,1) returns whatever precedes the ".".
The [5] returns the rightmost five characters.
Field(X,".",2,1) returns the 2nd "." delimited field.
[1,6] returns the first six characters.
Fmt(k,"L%6") returns k left justified on a background of six zeroes.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Thu, 28 Nov 2002 10:17:36 +0100
To: datastage-users@oliver.com
Subject: SQL types - formating fails
> Hi,
>
> Im having some problems with the dataformating of some decimal fields.
>
> Id like to make a field with a total length of 12, with 6 significant
> digits.
> ie. 12345.123456
>
> How should this be done in datastage ?
> This is what I did, and the result from different data values.
>
>
>
> SQL Type: Decimal
> Length: 12
> Scale: 6
>
> Input Output
> 123456789012345.123456789 123456789012345 // decimals are gone
> 1234567890.123456789 1234567890.12346 // decimal is rounded
> (1 lost)
> 12345.123456789 12345.123457 // decimal is rounded
> 123456789012.123456789 123456789012.123 // 12.6 ??????
>
> Hope someone can help.
> br
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
>
>
>
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
Im sure that would work - or something similar, but that is NOT what i
intend to implement in all my stages.
I actually thought that the meta data in DataStage had an affect, other
than for documentational purpose.
Isnt DataStage suposed to used them for anything ?
Atleast it knows that i cant put decimals into an integer, but why cant
it use the length and scale for anything ? Am I doing something wrong ???
br.
Tom
--------------------------------------------------------
Tom P. Nielsen
Project Masterdata
Reg.nr. 4853 / HH55.1.3
Nordea Bank Denmark
Phone: +45 33335223 / Plus nr. 435223
Ray Wurlod
cc:
Subject: Re: SQL types - formating fails
29-11-2002
02:46
Please
respond to
datastage-use
rs
Try the following, where X is the input value.
Field(X,".",1,1)[5]:".":Fmt(Field(X,".",2,1)[1,6],"L%6")
Field(X,".",1,1) returns whatever precedes the ".".
The [5] returns the rightmost five characters.
Field(X,".",2,1) returns the 2nd "." delimited field.
[1,6] returns the first six characters.
Fmt(k,"L%6") returns k left justified on a background of six zeroes.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Thu, 28 Nov 2002 10:17:36 +0100
To: datastage-users@oliver.com
Subject: SQL types - formating fails
> Hi,
>
> Im having some problems with the dataformating of some decimal fields.
>
> Id like to make a field with a total length of 12, with 6 significant
> digits.
> ie. 12345.123456
>
> How should this be done in datastage ?
> This is what I did, and the result from different data values.
>
>
>
> SQL Type: Decimal
> Length: 12
> Scale: 6
>
> Input Output
> 123456789012345.123456789 123456789012345 // decimals are gone
> 1234567890.123456789 1234567890.12346 // decimal is rounded
> (1 lost)
> 12345.123456789 12345.123457 // decimal is rounded
> 123456789012.123456789 123456789012.123 // 12.6 ??????
>
> Hope someone can help.
> br
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
>
>
>
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
intend to implement in all my stages.
I actually thought that the meta data in DataStage had an affect, other
than for documentational purpose.
Isnt DataStage suposed to used them for anything ?
Atleast it knows that i cant put decimals into an integer, but why cant
it use the length and scale for anything ? Am I doing something wrong ???
br.
Tom
--------------------------------------------------------
Tom P. Nielsen
Project Masterdata
Reg.nr. 4853 / HH55.1.3
Nordea Bank Denmark
Phone: +45 33335223 / Plus nr. 435223
Ray Wurlod
cc:
Subject: Re: SQL types - formating fails
29-11-2002
02:46
Please
respond to
datastage-use
rs
Try the following, where X is the input value.
Field(X,".",1,1)[5]:".":Fmt(Field(X,".",2,1)[1,6],"L%6")
Field(X,".",1,1) returns whatever precedes the ".".
The [5] returns the rightmost five characters.
Field(X,".",2,1) returns the 2nd "." delimited field.
[1,6] returns the first six characters.
Fmt(k,"L%6") returns k left justified on a background of six zeroes.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Thu, 28 Nov 2002 10:17:36 +0100
To: datastage-users@oliver.com
Subject: SQL types - formating fails
> Hi,
>
> Im having some problems with the dataformating of some decimal fields.
>
> Id like to make a field with a total length of 12, with 6 significant
> digits.
> ie. 12345.123456
>
> How should this be done in datastage ?
> This is what I did, and the result from different data values.
>
>
>
> SQL Type: Decimal
> Length: 12
> Scale: 6
>
> Input Output
> 123456789012345.123456789 123456789012345 // decimals are gone
> 1234567890.123456789 1234567890.12346 // decimal is rounded
> (1 lost)
> 12345.123456789 12345.123457 // decimal is rounded
> 123456789012.123456789 123456789012.123 // 12.6 ??????
>
> Hope someone can help.
> br
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
>
>
>
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
DataStage Engine (UniVerse) does not have a TIMESTAMP data type. This is why some of this problem is occurring.
In fact, DataStage Engine (UniVerse) does not have any data types at all, which is why it is tolerant of data type mismatches.
Table definition metadata, such as SQL data type, precision and scale, primarily record what is EXPECTED when accessing data sources; if they are not correct they can result in metadata mismatch warnings but, provided there is a compatible pair of metadata settings, will not prevent data from being processed. SQL data types do get used, however, to determine whether data can be moved into the other kind, as you noted.
DataStage Data Elements Date and Time do affect how dates and times are accessed via the ODBC and UV stage types (automatic conversion to/from DataStage internal format) but, so far as I have been able to determine, this capability was not extended to other stage types.
Finally, if you create a Transform based on the expression I originally suggested, then you need not (visibly at least) implement the expression in all of your jobs.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Tue, 3 Dec 2002 07:43:49 +0100
To: datastage-users@oliver.com
Subject: Re: SQL types - formating fails
>
> Im sure that would work - or something similar, but that is NOT what i
> intend to implement in all my stages.
>
> I actually thought that the meta data in DataStage had an affect, other
> than for documentational purpose.
> Isnt DataStage suposed to used them for anything ?
> Atleast it knows that i cant put decimals into an integer, but why cant
> it use the length and scale for anything ? Am I doing something wrong ???
>
> br.
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
In fact, DataStage Engine (UniVerse) does not have any data types at all, which is why it is tolerant of data type mismatches.
Table definition metadata, such as SQL data type, precision and scale, primarily record what is EXPECTED when accessing data sources; if they are not correct they can result in metadata mismatch warnings but, provided there is a compatible pair of metadata settings, will not prevent data from being processed. SQL data types do get used, however, to determine whether data can be moved into the other kind, as you noted.
DataStage Data Elements Date and Time do affect how dates and times are accessed via the ODBC and UV stage types (automatic conversion to/from DataStage internal format) but, so far as I have been able to determine, this capability was not extended to other stage types.
Finally, if you create a Transform based on the expression I originally suggested, then you need not (visibly at least) implement the expression in all of your jobs.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Tue, 3 Dec 2002 07:43:49 +0100
To: datastage-users@oliver.com
Subject: Re: SQL types - formating fails
>
> Im sure that would work - or something similar, but that is NOT what i
> intend to implement in all my stages.
>
> I actually thought that the meta data in DataStage had an affect, other
> than for documentational purpose.
> Isnt DataStage suposed to used them for anything ?
> Atleast it knows that i cant put decimals into an integer, but why cant
> it use the length and scale for anything ? Am I doing something wrong ???
>
> br.
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
Hi Ray,
I think you replyed to the other question regarding the timestamps, or
partialy.
My question was regaring the
decimal 12
scale 6
br
Tom
--------------------------------------------------------
Tom P. Nielsen
Project Masterdata
Reg.nr. 4853 / HH55.1.3
Nordea Bank Denmark
Phone: +45 33335223 / Plus nr. 435223
Ray Wurlod
cc:
Subject: Re: SQL types - formating fails
03-12-2002
07:38
Please
respond to
datastage-use
rs
DataStage Engine (UniVerse) does not have a TIMESTAMP data type. This is
why some of this problem is occurring.
In fact, DataStage Engine (UniVerse) does not have any data types at all,
which is why it is tolerant of data type mismatches.
Table definition metadata, such as SQL data type, precision and scale,
primarily record what is EXPECTED when accessing data sources; if they are
not correct they can result in metadata mismatch warnings but, provided
there is a compatible pair of metadata settings, will not prevent data from
being processed. SQL data types do get used, however, to determine whether
data can be moved into the other kind, as you noted.
DataStage Data Elements Date and Time do affect how dates and times are
accessed via the ODBC and UV stage types (automatic conversion to/from
DataStage internal format) but, so far as I have been able to determine,
this capability was not extended to other stage types.
Finally, if you create a Transform based on the expression I originally
suggested, then you need not (visibly at least) implement the expression in
all of your jobs.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Tue, 3 Dec 2002 07:43:49 +0100
To: datastage-users@oliver.com
Subject: Re: SQL types - formating fails
>
> Im sure that would work - or something similar, but that is NOT what i
> intend to implement in all my stages.
>
> I actually thought that the meta data in DataStage had an affect, other
> than for documentational purpose.
> Isnt DataStage suposed to used them for anything ?
> Atleast it knows that i cant put decimals into an integer, but why cant
> it use the length and scale for anything ? Am I doing something wrong ???
>
> br.
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
I think you replyed to the other question regarding the timestamps, or
partialy.
My question was regaring the
decimal 12
scale 6
br
Tom
--------------------------------------------------------
Tom P. Nielsen
Project Masterdata
Reg.nr. 4853 / HH55.1.3
Nordea Bank Denmark
Phone: +45 33335223 / Plus nr. 435223
Ray Wurlod
cc:
Subject: Re: SQL types - formating fails
03-12-2002
07:38
Please
respond to
datastage-use
rs
DataStage Engine (UniVerse) does not have a TIMESTAMP data type. This is
why some of this problem is occurring.
In fact, DataStage Engine (UniVerse) does not have any data types at all,
which is why it is tolerant of data type mismatches.
Table definition metadata, such as SQL data type, precision and scale,
primarily record what is EXPECTED when accessing data sources; if they are
not correct they can result in metadata mismatch warnings but, provided
there is a compatible pair of metadata settings, will not prevent data from
being processed. SQL data types do get used, however, to determine whether
data can be moved into the other kind, as you noted.
DataStage Data Elements Date and Time do affect how dates and times are
accessed via the ODBC and UV stage types (automatic conversion to/from
DataStage internal format) but, so far as I have been able to determine,
this capability was not extended to other stage types.
Finally, if you create a Transform based on the expression I originally
suggested, then you need not (visibly at least) implement the expression in
all of your jobs.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Tue, 3 Dec 2002 07:43:49 +0100
To: datastage-users@oliver.com
Subject: Re: SQL types - formating fails
>
> Im sure that would work - or something similar, but that is NOT what i
> intend to implement in all my stages.
>
> I actually thought that the meta data in DataStage had an affect, other
> than for documentational purpose.
> Isnt DataStage suposed to used them for anything ?
> Atleast it knows that i cant put decimals into an integer, but why cant
> it use the length and scale for anything ? Am I doing something wrong ???
>
> br.
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
I did think Id covered Decimal as well. Basically, DataStage itself sees the number as a string, and does not change the contents based on metadata.
You can achieve your desired result in a number of ways; I already suggested one way, using the Field() function and substrings. The Fmt() function can perform rounding or truncation to a particular number of decimal places, but you also wanted to truncate that part of the number to the left of the decimal place. You could do this mathematically with the Mod() function.
Come to think of it, and because of the no-data-type environment, you could get away with just the Mod() function.
In DataStage Mod(123456789.123455,1000000) returns 456789.1235 (if PRECISION is set to its default value, 4).
Hope this helps.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Tue, 3 Dec 2002 08:47:44 +0100
To: datastage-users@oliver.com
Subject: Re: SQL types - formating fails
>
> Hi Ray,
>
> I think you replyed to the other question regarding the timestamps, or
> partialy.
>
> My question was regaring the
> decimal 12
> scale 6
>
> br
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
>
>
>
>
> Ray Wurlod
> s.com> cc:
> Subject: Re: SQL types - formating fails
> 03-12-2002
> 07:38
> Please
> respond to
> datastage-use
> rs
>
>
>
>
> DataStage Engine (UniVerse) does not have a TIMESTAMP data type. This is
> why some of this problem is occurring.
>
> In fact, DataStage Engine (UniVerse) does not have any data types at all,
> which is why it is tolerant of data type mismatches.
>
> Table definition metadata, such as SQL data type, precision and scale,
> primarily record what is EXPECTED when accessing data sources; if they are
> not correct they can result in metadata mismatch warnings but, provided
> there is a compatible pair of metadata settings, will not prevent data from
> being processed. SQL data types do get used, however, to determine whether
> data can be moved into the other kind, as you noted.
> DataStage Data Elements Date and Time do affect how dates and times are
> accessed via the ODBC and UV stage types (automatic conversion to/from
> DataStage internal format) but, so far as I have been able to determine,
> this capability was not extended to other stage types.
>
> Finally, if you create a Transform based on the expression I originally
> suggested, then you need not (visibly at least) implement the expression in
> all of your jobs.
>
> ----- Original Message -----
> From: tom.peter.nielsen@nordea.com
> Date: Tue, 3 Dec 2002 07:43:49 +0100
> To: datastage-users@oliver.com
> Subject: Re: SQL types - formating fails
>
>
> >
> > Im sure that would work - or something similar, but that is NOT what i
> > intend to implement in all my stages.
> >
> > I actually thought that the meta data in DataStage had an affect, other
> > than for documentational purpose.
> > Isnt DataStage suposed to used them for anything ?
> > Atleast it knows that i cant put decimals into an integer, but why cant
> > it use the length and scale for anything ? Am I doing something wrong ???
> >
> > br.
> > Tom
> >
> > --------------------------------------------------------
> > Tom P. Nielsen
> > Project Masterdata
> > Reg.nr. 4853 / HH55.1.3
> > Nordea Bank Denmark
> >
> > Phone: +45 33335223 / Plus nr. 435223
>
> --
> __________________________________________________________
> Sign-up for your own FREE Personalized E-mail at Mail.com
> http://www.mail.com/?sr=signup
>
> One click access to the Top Search Engines
> http://www.exactsearchbar.com/mailcom
>
>
>
>
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom
You can achieve your desired result in a number of ways; I already suggested one way, using the Field() function and substrings. The Fmt() function can perform rounding or truncation to a particular number of decimal places, but you also wanted to truncate that part of the number to the left of the decimal place. You could do this mathematically with the Mod() function.
Come to think of it, and because of the no-data-type environment, you could get away with just the Mod() function.
In DataStage Mod(123456789.123455,1000000) returns 456789.1235 (if PRECISION is set to its default value, 4).
Hope this helps.
----- Original Message -----
From: tom.peter.nielsen@nordea.com
Date: Tue, 3 Dec 2002 08:47:44 +0100
To: datastage-users@oliver.com
Subject: Re: SQL types - formating fails
>
> Hi Ray,
>
> I think you replyed to the other question regarding the timestamps, or
> partialy.
>
> My question was regaring the
> decimal 12
> scale 6
>
> br
> Tom
>
> --------------------------------------------------------
> Tom P. Nielsen
> Project Masterdata
> Reg.nr. 4853 / HH55.1.3
> Nordea Bank Denmark
>
> Phone: +45 33335223 / Plus nr. 435223
>
>
>
>
> Ray Wurlod
> s.com> cc:
> Subject: Re: SQL types - formating fails
> 03-12-2002
> 07:38
> Please
> respond to
> datastage-use
> rs
>
>
>
>
> DataStage Engine (UniVerse) does not have a TIMESTAMP data type. This is
> why some of this problem is occurring.
>
> In fact, DataStage Engine (UniVerse) does not have any data types at all,
> which is why it is tolerant of data type mismatches.
>
> Table definition metadata, such as SQL data type, precision and scale,
> primarily record what is EXPECTED when accessing data sources; if they are
> not correct they can result in metadata mismatch warnings but, provided
> there is a compatible pair of metadata settings, will not prevent data from
> being processed. SQL data types do get used, however, to determine whether
> data can be moved into the other kind, as you noted.
> DataStage Data Elements Date and Time do affect how dates and times are
> accessed via the ODBC and UV stage types (automatic conversion to/from
> DataStage internal format) but, so far as I have been able to determine,
> this capability was not extended to other stage types.
>
> Finally, if you create a Transform based on the expression I originally
> suggested, then you need not (visibly at least) implement the expression in
> all of your jobs.
>
> ----- Original Message -----
> From: tom.peter.nielsen@nordea.com
> Date: Tue, 3 Dec 2002 07:43:49 +0100
> To: datastage-users@oliver.com
> Subject: Re: SQL types - formating fails
>
>
> >
> > Im sure that would work - or something similar, but that is NOT what i
> > intend to implement in all my stages.
> >
> > I actually thought that the meta data in DataStage had an affect, other
> > than for documentational purpose.
> > Isnt DataStage suposed to used them for anything ?
> > Atleast it knows that i cant put decimals into an integer, but why cant
> > it use the length and scale for anything ? Am I doing something wrong ???
> >
> > br.
> > Tom
> >
> > --------------------------------------------------------
> > Tom P. Nielsen
> > Project Masterdata
> > Reg.nr. 4853 / HH55.1.3
> > Nordea Bank Denmark
> >
> > Phone: +45 33335223 / Plus nr. 435223
>
> --
> __________________________________________________________
> Sign-up for your own FREE Personalized E-mail at Mail.com
> http://www.mail.com/?sr=signup
>
> One click access to the Top Search Engines
> http://www.exactsearchbar.com/mailcom
>
>
>
>
--
__________________________________________________________
Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup
One click access to the Top Search Engines
http://www.exactsearchbar.com/mailcom