none
convert varchar to float?

    Question

  • Is this possible?  I'm trying to user a lookup task and the data I want to compare is a varchar to float.  How can I do this?  I tried using the data conversion task and it didn't work and also tried cast and convert.  Is this even possible or is there a way around it?

     

     

    thanks,

    Friday, March 07, 2008 6:28 PM

Answers

  • I just went ahead and added another field to the table and made it a varchar and stored the value there.  Didn't want to do that but oh well, it works.  Thanks for the help.

    Sunday, March 09, 2008 1:07 AM
  • So you have table A (the source) with ColA (a varchar), and you have an OLE DB Source that queries this table. Then you have a lookup - does that lookup use table A or a different table as the reference table? If it uses another table (call it table B that has ColB (a float)), then the only thing you should have to do is cast ColB from the reference query in the lookup.

    Monday, March 10, 2008 1:02 PM

All replies

  • You cannot perform any implicit type conversion in a Lookup transformation. The data types must match exactly for any lookup.

     

    If you can provide more information about what steps you've tried and the error message(s) you get when they fail, that would be very helpful. If you convert the data types in your data flow to exactly match the data types in your lookup data, this should work fine - I do it all the time, so knowing what you're doing will be good.

    Friday, March 07, 2008 6:38 PM
  • so far in my dataflow task, i have tried doing a cast and convert on the field in the query part of my lookup task.  I get the error: one or more columns do not have supported data types, or their data types do not match.  So then I tried putting a data conversion task before the lookup and used double-precision float [dt _r8] and then I get the error: input column has a data type that cannot be joined on.  The input column is a varchar(max) and lookup column is a float.

     

    Friday, March 07, 2008 6:47 PM
  • I want to say that the Lookup transformation does not allow you to match based on Float values, because Floats (based on their inherently imprecise nature) cannot be meaningfully compared for equality. Unfortunately, I can't seem to find documentation to back me up on this right now, so you might want to do a little research to track it down. 

     

    Friday, March 07, 2008 7:02 PM
  • Matthew, you're correct that the lookup won't match on floats (or text, etc).

     

    Can't you cast the value in the lookup reference table to a varchar, and convert the value in the dataflow to a ansi string, so that they will match? Unfortunately, I don't know that they will actually match, because of the imprecision of floats.

     

    Saturday, March 08, 2008 11:57 PM
  • I just went ahead and added another field to the table and made it a varchar and stored the value there.  Didn't want to do that but oh well, it works.  Thanks for the help.

    Sunday, March 09, 2008 1:07 AM
  • Why did you have to add a column to the table? Couldn't you just convert the type in the query itself?

     

    Sunday, March 09, 2008 8:45 PM
  • I tried casting and converting the field in the OLE DB source query and got the error cannot join on datatype.  I also tried using a data conversion task and it didn't work as well.  So I just added a new field as varchar to my table but I would like to be able to figure this out in case it happens again in the future.
    Monday, March 10, 2008 11:59 AM
  • If the value from your source table is float, and the value from the reference table (the one the lookup uses) is float as well, you need to cast the value in two places. You need to do the cast in the query you specify for the Lookup transform against the reference table (you can't use the "table or view" option). And you need to either cast the value from the source table in the OLE DB Source, or use a Data Conversion task to convert it.

    Monday, March 10, 2008 12:43 PM
  • The value from the source table is a varchar.  I tried casting this value in the ole db source query not the table or view option.  I also tried using a data conversion task and still got the error. 

     

    Monday, March 10, 2008 12:50 PM
  • So you have table A (the source) with ColA (a varchar), and you have an OLE DB Source that queries this table. Then you have a lookup - does that lookup use table A or a different table as the reference table? If it uses another table (call it table B that has ColB (a float)), then the only thing you should have to do is cast ColB from the reference query in the lookup.

    Monday, March 10, 2008 1:02 PM
  • that works, I thought the the source column had to be the one casted.  I didn't know it was the column in the reference table, I guess I should have tried it that way.  thanks for the help.

     

    Monday, March 10, 2008 1:16 PM
  •  mr4100 wrote:
    that works, I thought the the source column had to be the one casted.  I didn't know it was the column in the reference table, I guess I should have tried it that way.  thanks for the help.

     

     

    The key thing is that the two columns (the one in the data flow and the one in the lookup) must have the same data type at the point in the data flow where the lookup is performed.

    Monday, March 10, 2008 1:21 PM