varchar to integer

Archive of postings to DataStageUsers@Oliver.com. This forum intended only as a reference and cannot be posted to.

Moderators: chulett, rschirm

Locked
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

varchar to integer

Post by admin »

Hello! I am working on a data conversion project, where I am pulling data from a varchar(100) field into an integer field. The values I am pulling are numeric (although this particular field can also contain characters, but for my purposes it is always an integer).

DataStage does not like the fact that I am pulling from a varchar and trying to insert into an integer field. What is the best way to convert the varchar to an integer to resolve my problem?

Here is my expression:

If UserPreftoTransform.preference_name = "Num_Res" Then 15 Else UserPreftoTransform.preference_value

The field I am trying to convert is:

UserPreftoTransform.preference_value

Thank you!

Lola

Lola M. Ball
netLibrary, Inc.
3085 Center Green Drive
Boulder, CO 80301
Direct 303.381.8922
Fax 303.381.8900
Email lball@netLibrary.com
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

DataStage does not have data types. It is probably the database server into which you are trying to insert the record that is giving the problem. Try writing the output into a sequential file to see what is actually being produced.

It may also be that UserPreftoTransform.preference_value contains non-numeric values (or non-integer values). Test explicitly for this if its a possibility. For example the Num() function reports whether a string can be regarded as numeric:

If UserPreftoTransform.preference_name = "Num_Res"
OR Not(Num(UserPreftoTransform.preference_value))
Then 15 Else Int(UserPreftoTransform.preference_value + 0)

A different test - explicitly for integer - is (UserPreftoTransform.preference_value Matches "1N0N")

Varchar(100) may have leading or trailing white space characters. Performing arithmetic (adding zero in the above example) is a convenient way to get rid of this, since arithmetic always returns the shortest possible string. The Int() function returns just the integer component of a number, in case you have 4.7 in the UserPreftoTransform.preference_value column.

-----Original Message-----
From: Lola Ball [mailto:lball@netLibrary.com]
Sent: Thursday, 10 May 2001 03:27
To: Datastage (E-mail)
Subject: varchar to integer


Hello! I am working on a data conversion project, where I am pulling data from a varchar(100) field into an integer field. The values I am pulling are numeric (although this particular field can also contain characters, but for my purposes it is always an integer).

DataStage does not like the fact that I am pulling from a varchar and trying to insert into an integer field. What is the best way to convert the varchar to an integer to resolve my problem?

Here is my expression:

If UserPreftoTransform.preference_name = "Num_Res" Then 15 Else UserPreftoTransform.preference_value

The field I am trying to convert is:

UserPreftoTransform.preference_value

Thank you!

Lola

Lola M. Ball
netLibrary, Inc.
3085 Center Green Drive
Boulder, CO 80301
Direct 303.381.8922
Fax 303.381.8900
Email lball@netLibrary.com
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Which brings up a very fundamental question not explained in the class I took. What is the purpose of having both and SQL type and a Data Element? Ive used both ways--just defining SQL type of something like INT and either using or not using a Data Element of Numeric. I couldnt see where it made a difference?

What concept am I missing here?

Regards,

Clif


Wednesday, May 09, 2001, 5:48:28 PM, you wrote:

> DataStage is typeless
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

In addition you might consider simply changing the data type in the output link to integer (if you have not done so) along with the trim() function on
the input column to remove spaces as Ray indicated. DataStage is typeless
(more or less) so to change from one data type to another is simply changing the "meta data" in the link/column definition. This will be used only by the source/target stage if appropriate.

Regards,

Allen Spayth


----- Original Message -----
From: "Ray Wurlod"
To:
Sent: Wednesday, May 09, 2001 3:21 PM
Subject: RE: varchar to integer


> DataStage does not have data types. It is probably the database
> server
into
> which you are trying to insert the record that is giving the problem.
> Try writing the output into a sequential file to see what is actually
> being produced.
>
> It may also be that UserPreftoTransform.preference_value contains
> non-numeric values (or non-integer values). Test explicitly for this
> if its a possibility. For example the Num() function reports whether
> a
string
> can be regarded as numeric:
>
> If UserPreftoTransform.preference_name = "Num_Res"
> OR Not(Num(UserPreftoTransform.preference_value))
> Then 15 Else Int(UserPreftoTransform.preference_value + 0)
>
> A different test - explicitly for integer - is
> (UserPreftoTransform.preference_value Matches "1N0N")
>
> Varchar(100) may have leading or trailing white space characters.
> Performing arithmetic (adding zero in the above example) is a
> convenient
way
> to get rid of this, since arithmetic always returns the shortest
> possible string. The Int() function returns just the integer
> component of a
number,
> in case you have 4.7 in the UserPreftoTransform.preference_value
> column.
>
> -----Original Message-----
> From: Lola Ball [mailto:lball@netLibrary.com]
> Sent: Thursday, 10 May 2001 03:27
> To: Datastage (E-mail)
> Subject: varchar to integer
>
>
> Hello! I am working on a data conversion project, where I am pulling
> data from a varchar(100) field into an integer field. The values I am
> pulling are numeric (although this particular field can also contain
> characters,
but
> for my purposes it is always an integer).
>
> DataStage does not like the fact that I am pulling from a varchar and
trying
> to insert into an integer field. What is the best way to convert the
> varchar to an integer to resolve my problem?
>
> Here is my expression:
>
> If UserPreftoTransform.preference_name = "Num_Res" Then 15 Else
> UserPreftoTransform.preference_value
>
> The field I am trying to convert is:
>
> UserPreftoTransform.preference_value
>
> Thank you!
>
> Lola
>
> Lola M. Ball
> netLibrary, Inc.
> 3085 Center Green Drive
> Boulder, CO 80301
> Direct 303.381.8922
> Fax 303.381.8900
> Email lball@netLibrary.com
>
>
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Thank you very much for your suggestions. I guess I am still a little at a loss -- the column does contain both numeric and character values. The situation is this:

I have a table that stores default preferences -- with a preference name and default preference value -- this is the varchar field, that sometimes represents a number and other times a word. Then, I have another table that stores the user preference and there is a one to many between default preference and user preference. In this case, I have specified which preference name I am after ("Num_Res") and so I know that the preference values for it are always numeric.

I looked at the Int() function, and now it either returns 15 (from the "Then" part of the If Then Else) or 0, from the Int(UserPreftoTransform.preference_value + 0). However, the possible values for this field are many: 15, 30, 40, etc.

Let me know what you think.

Thank you so much -- I really appreciate it!!

Lola

-----Original Message-----
From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
Sent: Wednesday, May 09, 2001 4:48 PM
To: informix-datastage@oliver.com
Subject: Re: varchar to integer


In addition you might consider simply changing the data type in the output link to integer (if you have not done so) along with the trim() function on
the input column to remove spaces as Ray indicated. DataStage is typeless
(more or less) so to change from one data type to another is simply changing the "meta data" in the link/column definition. This will be used only by the source/target stage if appropriate.

Regards,

Allen Spayth


----- Original Message -----
From: "Ray Wurlod"
To:
Sent: Wednesday, May 09, 2001 3:21 PM
Subject: RE: varchar to integer


> DataStage does not have data types. It is probably the database
> server
into
> which you are trying to insert the record that is giving the problem.
> Try writing the output into a sequential file to see what is actually
> being produced.
>
> It may also be that UserPreftoTransform.preference_value contains
> non-numeric values (or non-integer values). Test explicitly for this
> if its a possibility. For example the Num() function reports whether
> a
string
> can be regarded as numeric:
>
> If UserPreftoTransform.preference_name = "Num_Res"
> OR Not(Num(UserPreftoTransform.preference_value))
> Then 15 Else Int(UserPreftoTransform.preference_value + 0)
>
> A different test - explicitly for integer - is
> (UserPreftoTransform.preference_value Matches "1N0N")
>
> Varchar(100) may have leading or trailing white space characters.
> Performing arithmetic (adding zero in the above example) is a
> convenient
way
> to get rid of this, since arithmetic always returns the shortest
> possible string. The Int() function returns just the integer
> component of a
number,
> in case you have 4.7 in the UserPreftoTransform.preference_value
> column.
>
> -----Original Message-----
> From: Lola Ball [mailto:lball@netLibrary.com]
> Sent: Thursday, 10 May 2001 03:27
> To: Datastage (E-mail)
> Subject: varchar to integer
>
>
> Hello! I am working on a data conversion project, where I am pulling
> data from a varchar(100) field into an integer field. The values I am
> pulling are numeric (although this particular field can also contain
> characters,
but
> for my purposes it is always an integer).
>
> DataStage does not like the fact that I am pulling from a varchar and
trying
> to insert into an integer field. What is the best way to convert the
> varchar to an integer to resolve my problem?
>
> Here is my expression:
>
> If UserPreftoTransform.preference_name = "Num_Res" Then 15 Else
> UserPreftoTransform.preference_value
>
> The field I am trying to convert is:
>
> UserPreftoTransform.preference_value
>
> Thank you!
>
> Lola
>
> Lola M. Ball
> netLibrary, Inc.
> 3085 Center Green Drive
> Boulder, CO 80301
> Direct 303.381.8922
> Fax 303.381.8900
> Email lball@netLibrary.com
>
>
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Lola,

Going back to something Ray suggested - have you tried to simply output this to a sequential file? Then view the output to see what it is outputting?. Try to confirm that the output when you have your constraint in place is actually outputting the 15, 30, 40 etc type of data values that you expect to the sequential file. Also remember that perhaps there is some padding
(spaces) in the data along with the numeric data such that it is being interpreted as a string by the data base (the sequential file will not care about this) - these can be eliminated by appling the TRIM() function on the column.

Regards,

Allen
----- Original Message -----
From: "Lola Ball"
To:
Sent: Wednesday, May 09, 2001 4:12 PM
Subject: RE: varchar to integer


> Thank you very much for your suggestions. I guess I am still a little
> at
a
> loss -- the column does contain both numeric and character values.
> The situation is this:
>
> I have a table that stores default preferences -- with a preference
> name
and
> default preference value -- this is the varchar field, that sometimes
> represents a number and other times a word. Then, I have another
> table
that
> stores the user preference and there is a one to many between default
> preference and user preference. In this case, I have specified which
> preference name I am after ("Num_Res") and so I know that the
> preference values for it are always numeric.
>
> I looked at the Int() function, and now it either returns 15 (from the
> "Then" part of the If Then Else) or 0, from the
> Int(UserPreftoTransform.preference_value + 0). However, the possible
values
> for this field are many: 15, 30, 40, etc.
>
> Let me know what you think.
>
> Thank you so much -- I really appreciate it!!
>
> Lola
>
> -----Original Message-----
> From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
> Sent: Wednesday, May 09, 2001 4:48 PM
> To: informix-datastage@oliver.com
> Subject: Re: varchar to integer
>
>
> In addition you might consider simply changing the data type in the
> output link to integer (if you have not done so) along with the trim()
> function
on
> the input column to remove spaces as Ray indicated. DataStage is
typeless
> (more or less) so to change from one data type to another is simply
changing
> the "meta data" in the link/column definition. This will be used only
> by the source/target stage if appropriate.
>
> Regards,
>
> Allen Spayth
>
>
> ----- Original Message -----
> From: "Ray Wurlod"
> To:
> Sent: Wednesday, May 09, 2001 3:21 PM
> Subject: RE: varchar to integer
>
>
> > DataStage does not have data types. It is probably the database
> > server
> into
> > which you are trying to insert the record that is giving the
> > problem.
Try
> > writing the output into a sequential file to see what is actually
> > being produced.
> >
> > It may also be that UserPreftoTransform.preference_value contains
> > non-numeric values (or non-integer values). Test explicitly for
> > this if its a possibility. For example the Num() function reports
> > whether a
> string
> > can be regarded as numeric:
> >
> > If UserPreftoTransform.preference_name = "Num_Res"
> > OR Not(Num(UserPreftoTransform.preference_value))
> > Then 15 Else Int(UserPreftoTransform.preference_value + 0)
> >
> > A different test - explicitly for integer - is
> > (UserPreftoTransform.preference_value Matches "1N0N")
> >
> > Varchar(100) may have leading or trailing white space characters.
> > Performing arithmetic (adding zero in the above example) is a
> > convenient
> way
> > to get rid of this, since arithmetic always returns the shortest
possible
> > string. The Int() function returns just the integer component of a
> number,
> > in case you have 4.7 in the UserPreftoTransform.preference_value
> > column.
> >
> > -----Original Message-----
> > From: Lola Ball [mailto:lball@netLibrary.com]
> > Sent: Thursday, 10 May 2001 03:27
> > To: Datastage (E-mail)
> > Subject: varchar to integer
> >
> >
> > Hello! I am working on a data conversion project, where I am
> > pulling
data
> > from a varchar(100) field into an integer field. The values I am
pulling
> > are numeric (although this particular field can also contain
> > characters,
> but
> > for my purposes it is always an integer).
> >
> > DataStage does not like the fact that I am pulling from a varchar
> > and
> trying
> > to insert into an integer field. What is the best way to convert
> > the varchar to an integer to resolve my problem?
> >
> > Here is my expression:
> >
> > If UserPreftoTransform.preference_name = "Num_Res" Then 15 Else
> > UserPreftoTransform.preference_value
> >
> > The field I am trying to convert is:
> >
> > UserPreftoTransform.preference_value
> >
> > Thank you!
> >
> > Lola
> >
> > Lola M. Ball
> > netLibrary, Inc.
> > 3085 Center Green Drive
> > Boulder, CO 80301
> > Direct 303.381.8922
> > Fax 303.381.8900
> > Email lball@netLibrary.com
> >
> >
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Allen --

I tried outputting to a sequential file, and got the same results. I also got the following warning message:

DataStage Job 9 Phantom 2124
Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used. Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used. Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used. Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used. Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used. Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used. Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used. Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used. Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when numeric required. Zero used.

This error message repeats many more times. I checked line 77 of the data, and there is a numeric value with no space in that field. So, I am not sure what is going on here.

Any ideas?

Thank you!

Lola

-----Original Message-----
From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
Sent: Wednesday, May 09, 2001 7:16 PM
To: informix-datastage@oliver.com
Subject: Re: varchar to integer


Lola,

Going back to something Ray suggested - have you tried to simply output this to a sequential file? Then view the output to see what it is outputting?. Try to confirm that the output when you have your constraint in place is actually outputting the 15, 30, 40 etc type of data values that you expect to the sequential file. Also remember that perhaps there is some padding
(spaces) in the data along with the numeric data such that it is being interpreted as a string by the data base (the sequential file will not care about this) - these can be eliminated by appling the TRIM() function on the column.

Regards,

Allen
----- Original Message -----
From: "Lola Ball"
To:
Sent: Wednesday, May 09, 2001 4:12 PM
Subject: RE: varchar to integer


> Thank you very much for your suggestions. I guess I am still a little
> at
a
> loss -- the column does contain both numeric and character values.
> The situation is this:
>
> I have a table that stores default preferences -- with a preference
> name
and
> default preference value -- this is the varchar field, that sometimes
> represents a number and other times a word. Then, I have another
> table
that
> stores the user preference and there is a one to many between default
> preference and user preference. In this case, I have specified which
> preference name I am after ("Num_Res") and so I know that the
> preference values for it are always numeric.
>
> I looked at the Int() function, and now it either returns 15 (from the
> "Then" part of the If Then Else) or 0, from the
> Int(UserPreftoTransform.preference_value + 0). However, the possible
values
> for this field are many: 15, 30, 40, etc.
>
> Let me know what you think.
>
> Thank you so much -- I really appreciate it!!
>
> Lola
>
> -----Original Message-----
> From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
> Sent: Wednesday, May 09, 2001 4:48 PM
> To: informix-datastage@oliver.com
> Subject: Re: varchar to integer
>
>
> In addition you might consider simply changing the data type in the
> output link to integer (if you have not done so) along with the trim()
> function
on
> the input column to remove spaces as Ray indicated. DataStage is
typeless
> (more or less) so to change from one data type to another is simply
changing
> the "meta data" in the link/column definition. This will be used only
> by the source/target stage if appropriate.
>
> Regards,
>
> Allen Spayth
>
>
> ----- Original Message -----
> From: "Ray Wurlod"
> To:
> Sent: Wednesday, May 09, 2001 3:21 PM
> Subject: RE: varchar to integer
>
>
> > DataStage does not have data types. It is probably the database
> > server
> into
> > which you are trying to insert the record that is giving the
> > problem.
Try
> > writing the output into a sequential file to see what is actually
> > being produced.
> >
> > It may also be that UserPreftoTransform.preference_value contains
> > non-numeric values (or non-integer values). Test explicitly for
> > this if its a possibility. For example the Num() function reports
> > whether a
> string
> > can be regarded as numeric:
> >
> > If UserPreftoTransform.preference_name = "Num_Res"
> > OR Not(Num(UserPreftoTransform.preference_value))
> > Then 15 Else Int(UserPreftoTransform.preference_value + 0)
> >
> > A different test - explicitly for integer - is
> > (UserPreftoTransform.preference_value Matches "1N0N")
> >
> > Varchar(100) may have leading or trailing white space characters.
> > Performing arithmetic (adding zero in the above example) is a
> > convenient
> way
> > to get rid of this, since arithmetic always returns the shortest
possible
> > string. The Int() function returns just the integer component of a
> number,
> > in case you have 4.7 in the UserPreftoTransform.preference_value
> > column.
> >
> > -----Original Message-----
> > From: Lola Ball [mailto:lball@netLibrary.com]
> > Sent: Thursday, 10 May 2001 03:27
> > To: Datastage (E-mail)
> > Subject: varchar to integer
> >
> >
> > Hello! I am working on a data conversion project, where I am
> > pulling
data
> > from a varchar(100) field into an integer field. The values I am
pulling
> > are numeric (although this particular field can also contain
> > characters,
> but
> > for my purposes it is always an integer).
> >
> > DataStage does not like the fact that I am pulling from a varchar
> > and
> trying
> > to insert into an integer field. What is the best way to convert
> > the varchar to an integer to resolve my problem?
> >
> > Here is my expression:
> >
> > If UserPreftoTransform.preference_name = "Num_Res" Then 15 Else
> > UserPreftoTransform.preference_value
> >
> > The field I am trying to convert is:
> >
> > UserPreftoTransform.preference_value
> >
> > Thank you!
> >
> > Lola
> >
> > Lola M. Ball
> > netLibrary, Inc.
> > 3085 Center Green Drive
> > Boulder, CO 80301
> > Direct 303.381.8922
> > Fax 303.381.8900
> > Email lball@netLibrary.com
> >
> >
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Lola,

The error you are recieving relates to the line number of the executable code not the actual data. Usually it is associated with a calculation with a none numeric data value (this may be causing it; Int(UserPreftoTransform.preference_value + 0)).

Try creating a very simple job with no transformations in it but still use your constraint to pick out the numeric value rows, as well output it to a sequencial file.

Let me know what the results are.

Regards,

Allen
----- Original Message -----
From: "Lola Ball"
To:
Sent: Thursday, May 10, 2001 7:08 AM
Subject: RE: varchar to integer


> Allen --
>
> I tried outputting to a sequential file, and got the same results. I
> also got the following warning message:
>
> DataStage Job 9 Phantom 2124
> Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data
> when numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used.
>
> This error message repeats many more times. I checked line 77 of the
data,
> and there is a numeric value with no space in that field. So, I am
> not
sure
> what is going on here.
>
> Any ideas?
>
> Thank you!
>
> Lola
>
> -----Original Message-----
> From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
> Sent: Wednesday, May 09, 2001 7:16 PM
> To: informix-datastage@oliver.com
> Subject: Re: varchar to integer
>
>
> Lola,
>
> Going back to something Ray suggested - have you tried to simply
> output
this
> to a sequential file? Then view the output to see what it is
> outputting?. Try to confirm that the output when you have your
> constraint in place is actually outputting the 15, 30, 40 etc type of
> data values that you expect to the sequential file. Also remember
> that perhaps there is some padding
> (spaces) in the data along with the numeric data such that it is being
> interpreted as a string by the data base (the sequential file will not
care
> about this) - these can be eliminated by appling the TRIM() function
> on
the
> column.
>
> Regards,
>
> Allen
> ----- Original Message -----
> From: "Lola Ball"
> To:
> Sent: Wednesday, May 09, 2001 4:12 PM
> Subject: RE: varchar to integer
>
>
> > Thank you very much for your suggestions. I guess I am still a
> > little
at
> a
> > loss -- the column does contain both numeric and character values.
> > The situation is this:
> >
> > I have a table that stores default preferences -- with a preference
> > name
> and
> > default preference value -- this is the varchar field, that
> > sometimes represents a number and other times a word. Then, I have
> > another table
> that
> > stores the user preference and there is a one to many between
> > default preference and user preference. In this case, I have
> > specified which preference name I am after ("Num_Res") and so I know
> > that the preference values for it are always numeric.
> >
> > I looked at the Int() function, and now it either returns 15 (from
> > the "Then" part of the If Then Else) or 0, from the
> > Int(UserPreftoTransform.preference_value + 0). However, the
> > possible
> values
> > for this field are many: 15, 30, 40, etc.
> >
> > Let me know what you think.
> >
> > Thank you so much -- I really appreciate it!!
> >
> > Lola
> >
> > -----Original Message-----
> > From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
> > Sent: Wednesday, May 09, 2001 4:48 PM
> > To: informix-datastage@oliver.com
> > Subject: Re: varchar to integer
> >
> >
> > In addition you might consider simply changing the data type in the
output
> > link to integer (if you have not done so) along with the trim()
> > function
> on
> > the input column to remove spaces as Ray indicated. DataStage is
> typeless
> > (more or less) so to change from one data type to another is simply
> changing
> > the "meta data" in the link/column definition. This will be used
> > only
by
> > the source/target stage if appropriate.
> >
> > Regards,
> >
> > Allen Spayth
> >
> >
> > ----- Original Message -----
> > From: "Ray Wurlod"
> > To:
> > Sent: Wednesday, May 09, 2001 3:21 PM
> > Subject: RE: varchar to integer
> >
> >
> > > DataStage does not have data types. It is probably the database
server
> > into
> > > which you are trying to insert the record that is giving the
> > > problem.
> Try
> > > writing the output into a sequential file to see what is actually
being
> > > produced.
> > >
> > > It may also be that UserPreftoTransform.preference_value contains
> > > non-numeric values (or non-integer values). Test explicitly for
> > > this
if
> > > its a possibility. For example the Num() function reports
> > > whether a
> > string
> > > can be regarded as numeric:
> > >
> > > If UserPreftoTransform.preference_name = "Num_Res"
> > > OR Not(Num(UserPreftoTransform.preference_value))
> > > Then 15 Else Int(UserPreftoTransform.preference_value + 0)
> > >
> > > A different test - explicitly for integer - is
> > > (UserPreftoTransform.preference_value Matches "1N0N")
> > >
> > > Varchar(100) may have leading or trailing white space characters.
> > > Performing arithmetic (adding zero in the above example) is a
convenient
> > way
> > > to get rid of this, since arithmetic always returns the shortest
> possible
> > > string. The Int() function returns just the integer component of
> > > a
> > number,
> > > in case you have 4.7 in the UserPreftoTransform.preference_value
column.
> > >
> > > -----Original Message-----
> > > From: Lola Ball [mailto:lball@netLibrary.com]
> > > Sent: Thursday, 10 May 2001 03:27
> > > To: Datastage (E-mail)
> > > Subject: varchar to integer
> > >
> > >
> > > Hello! I am working on a data conversion project, where I am
> > > pulling
> data
> > > from a varchar(100) field into an integer field. The values I am
> pulling
> > > are numeric (although this particular field can also contain
characters,
> > but
> > > for my purposes it is always an integer).
> > >
> > > DataStage does not like the fact that I am pulling from a varchar
> > > and
> > trying
> > > to insert into an integer field. What is the best way to convert
> > > the varchar to an integer to resolve my problem?
> > >
> > > Here is my expression:
> > >
> > > If UserPreftoTransform.preference_name = "Num_Res" Then 15 Else
> > > UserPreftoTransform.preference_value
> > >
> > > The field I am trying to convert is:
> > >
> > > UserPreftoTransform.preference_value
> > >
> > > Thank you!
> > >
> > > Lola
> > >
> > > Lola M. Ball
> > > netLibrary, Inc.
> > > 3085 Center Green Drive
> > > Boulder, CO 80301
> > > Direct 303.381.8922
> > > Fax 303.381.8900
> > > Email lball@netLibrary.com
> > >
> > >
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Allen --

It turns out the problem was not in the data I was reading and its type, but rather how it as coming in and how I wanted it to go out. I was reading in multiple rows that I wanted to output as one row with multiple columns. The problem with datatype turned out to be a different field entirely -- a data field.

In fact, this date problem is continuing to give me issues. In SQL Server, there is a data type of smalldatetime, with size 4. However, when you pull this field into DataStage, it reads it as timestamp 16. Then when you try to push it back out to SQL Server, it gives you an error since SQL Server does not want to put a timestamp of 16 into a smalldatetime of 4. I am working on using Oconv and Iconv right now to get the time converted properly, but am not having good luck. Here is what I have:

Oconv(Iconv(AcctFiletoAcctTransform.last_login,"D4-YMD[4,2,2]"), "D
MDY[Z,Z,4]")

I have also tried:

Oconv(Iconv(AcctFiletoAcctTransform.last_login,"D4-YMD[4,2,2]"), "D
DMY[,A,]")

Neither is working.

Any ideas?

Thank you so much for your help!

Lola


-----Original Message-----
From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
Sent: Thursday, May 10, 2001 9:05 AM
To: informix-datastage@oliver.com
Subject: Re: varchar to integer


Lola,

The error you are recieving relates to the line number of the executable code not the actual data. Usually it is associated with a calculation with a none numeric data value (this may be causing it; Int(UserPreftoTransform.preference_value + 0)).

Try creating a very simple job with no transformations in it but still use your constraint to pick out the numeric value rows, as well output it to a sequencial file.

Let me know what the results are.

Regards,

Allen
----- Original Message -----
From: "Lola Ball"
To:
Sent: Thursday, May 10, 2001 7:08 AM
Subject: RE: varchar to integer


> Allen --
>
> I tried outputting to a sequential file, and got the same results. I
> also got the following warning message:
>
> DataStage Job 9 Phantom 2124
> Program "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data
> when numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used. Program
> "JOB.1066924084.DT.1218428485.V0S3": Line 77, Nonnumeric data when
> numeric required. Zero used.
>
> This error message repeats many more times. I checked line 77 of the
data,
> and there is a numeric value with no space in that field. So, I am
> not
sure
> what is going on here.
>
> Any ideas?
>
> Thank you!
>
> Lola
>
> -----Original Message-----
> From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
> Sent: Wednesday, May 09, 2001 7:16 PM
> To: informix-datastage@oliver.com
> Subject: Re: varchar to integer
>
>
> Lola,
>
> Going back to something Ray suggested - have you tried to simply
> output
this
> to a sequential file? Then view the output to see what it is
> outputting?. Try to confirm that the output when you have your
> constraint in place is actually outputting the 15, 30, 40 etc type of
> data values that you expect to the sequential file. Also remember
> that perhaps there is some padding
> (spaces) in the data along with the numeric data such that it is being
> interpreted as a string by the data base (the sequential file will not
care
> about this) - these can be eliminated by appling the TRIM() function
> on
the
> column.
>
> Regards,
>
> Allen
> ----- Original Message -----
> From: "Lola Ball"
> To:
> Sent: Wednesday, May 09, 2001 4:12 PM
> Subject: RE: varchar to integer
>
>
> > Thank you very much for your suggestions. I guess I am still a
> > little
at
> a
> > loss -- the column does contain both numeric and character values.
> > The situation is this:
> >
> > I have a table that stores default preferences -- with a preference
> > name
> and
> > default preference value -- this is the varchar field, that
> > sometimes represents a number and other times a word. Then, I have
> > another table
> that
> > stores the user preference and there is a one to many between
> > default preference and user preference. In this case, I have
> > specified which preference name I am after ("Num_Res") and so I know
> > that the preference values for it are always numeric.
> >
> > I looked at the Int() function, and now it either returns 15 (from
> > the "Then" part of the If Then Else) or 0, from the
> > Int(UserPreftoTransform.preference_value + 0). However, the
> > possible
> values
> > for this field are many: 15, 30, 40, etc.
> >
> > Let me know what you think.
> >
> > Thank you so much -- I really appreciate it!!
> >
> > Lola
> >
> > -----Original Message-----
> > From: Allen Spayth - wwc [mailto:daspayth@wwc.com]
> > Sent: Wednesday, May 09, 2001 4:48 PM
> > To: informix-datastage@oliver.com
> > Subject: Re: varchar to integer
> >
> >
> > In addition you might consider simply changing the data type in the
output
> > link to integer (if you have not done so) along with the trim()
> > function
> on
> > the input column to remove spaces as Ray indicated. DataStage is
> typeless
> > (more or less) so to change from one data type to another is simply
> changing
> > the "meta data" in the link/column definition. This will be used
> > only
by
> > the source/target stage if appropriate.
> >
> > Regards,
> >
> > Allen Spayth
> >
> >
> > ----- Original Message -----
> > From: "Ray Wurlod"
> > To:
> > Sent: Wednesday, May 09, 2001 3:21 PM
> > Subject: RE: varchar to integer
> >
> >
> > > DataStage does not have data types. It is probably the database
server
> > into
> > > which you are trying to insert the record that is giving the
> > > problem.
> Try
> > > writing the output into a sequential file to see what is actually
being
> > > produced.
> > >
> > > It may also be that UserPreftoTransform.preference_value contains
> > > non-numeric values (or non-integer values). Test explicitly for
> > > this
if
> > > its a possibility. For example the Num() function reports
> > > whether a
> > string
> > > can be regarded as numeric:
> > >
> > > If UserPreftoTransform.preference_name = "Num_Res"
> > > OR Not(Num(UserPreftoTransform.preference_value))
> > > Then 15 Else Int(UserPreftoTransform.preference_value + 0)
> > >
> > > A different test - explicitly for integer - is
> > > (UserPreftoTransform.preference_value Matches "1N0N")
> > >
> > > Varchar(100) may have leading or trailing white space characters.
> > > Performing arithmetic (adding zero in the above example) is a
convenient
> > way
> > > to get rid of this, since arithmetic always returns the shortest
> possible
> > > string. The Int() function returns just the integer component of
> > > a
> > number,
> > > in case you have 4.7 in the UserPreftoTransform.preference_value
column.
> > >
> > > -----Original Message-----
> > > From: Lola Ball [mailto:lball@netLibrary.com]
> > > Sent: Thursday, 10 May 2001 03:27
> > > To: Datastage (E-mail)
> > > Subject: varchar to integer
> > >
> > >
> > > Hello! I am working on a data conversion project, where I am
> > > pulling
> data
> > > from a varchar(100) field into an integer field. The values I am
> pulling
> > > are numeric (although this particular field can also contain
characters,
> > but
> > > for my purposes it is always an integer).
> > >
> > > DataStage does not like the fact that I am pulling from a varchar
> > > and
> > trying
> > > to insert into an integer field. What is the best way to convert
> > > the varchar to an integer to resolve my problem?
> > >
> > > Here is my expression:
> > >
> > > If UserPreftoTransform.preference_name = "Num_Res" Then 15 Else
> > > UserPreftoTransform.preference_value
> > >
> > > The field I am trying to convert is:
> > >
> > > UserPreftoTransform.preference_value
> > >
> > > Thank you!
> > >
> > > Lola
> > >
> > > Lola M. Ball
> > > netLibrary, Inc.
> > > 3085 Center Green Drive
> > > Boulder, CO 80301
> > > Direct 303.381.8922
> > > Fax 303.381.8900
> > > Email lball@netLibrary.com
> > >
> > >
Locked