3 Replies Latest reply on Sep 15, 2017 2:45 PM by Hari Ankem

    varchar to float problem (latitude/longitude)

    Jeff Strahl

      Hello all,

       

      I am attempting to connect to a SQL 2012 database, and query some numbers from a VARCHAR column to convert those to latitude and longitude values in order that I might plot them on a map. I don't have any control over the source database/data for it is what supports our third-party billing system.

       

      The source data looks like:

      CapturFiles-201709257_150945.png

       

      After a bit of searching, I found a method to use to convert the values from strings to floats, and I used them. I am not sure that "Money" is relevant, but I left it there and it worked.

       

      For longitude, I created a calculated column, with the following calculation:

      RAWSQL_REAL("CASE WHEN ISNUMERIC(%1) = 1 THEN CAST(CAST(%1 AS MONEY) AS FLOAT) END", [PackageLong])

       

      It validates and the numbers come back as expected.

       

      For latitude, I followed what worked for longitude:

      RAWSQL_REAL("CASE WHEN ISNUMERIC(%1) = 1 THEN CAST(CAST(%1 AS MONEY) AS FLOAT) END", [PackageLat])

       

      And that seemed to work.

       

      Now they are Number (decimal) and I have assigned them longitude & latitude geographic roles, respectively.

       

      When I attempt to plot them, I drag the Longitude dimension to the Columns, and it works as expected. However, when I drag the Latitude dimension to the rows, I get the following error:

      [SqlServerODBC] (60) Response error: Cannot convert a char value to money. The char value has incorrect syntax., .

       

      But it works correctly on the 'Data Source' side of things. So, I don't know if this is a bug or if I am simply doing something incorrectly. I'm open to any feedback or guidance that you can give me, for I am stuck and this report is of course due yesterday.

       

      Thanks