#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0

    Syntax error converting the varchar value, in need of some pointers


    With the code below I get a error

    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value 'LTORFPARENT' to a column of data type int.

    Where I am querying integers but there are some alphanumeric(ex. 333a) in there columns as well. Is there a way around this?

    Thanks

    Code:
    SELECT     dbo.meterDefineDPView.meterName, dbo.hourlyMeterReading.contractTime, dbo.hourlyMeterReading.rawValue, dbo.listDefine.listName, dbo.objectDefine.objectName
    FROM         dbo.meterDefineDPView INNER JOIN
                              (SELECT     meteriD, MAX(rawValue) AS [Differential]
                                FROM          dbo.hourlyMeterReading
                                WHERE      dataQuality IN ('10', '11') AND contractTime BETWEEN CONVERT(DATETIME, '2012-May-14', 102) AND CONVERT(DATETIME, 
                                                       '2013-May-14', 102)
                                GROUP BY meterId) maxes ON maxes.meteriD = dbo.meterDefineDPView.meterName
    INNER JOIN
                          dbo.objectDefine ON dbo.meterDefineDPView.meterName = dbo.objectDefine.objectName INNER JOIN
                          dbo.listRefer ON dbo.objectDefine.objectId = dbo.listRefer.objectId INNER JOIN
                          dbo.hourlyMeterReading ON dbo.meterDefineDPView.meterId = dbo.hourlyMeterReading.meterId INNER JOIN
                          dbo.listDefine ON dbo.listRefer.listId = dbo.listDefine.listId
    WHERE     (dbo.listDefine.listName = 'orifice meter')
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    this depends a bit on your data. if it's simple alphanumerics, you can use implicit conversion like 0 + whatever .
    in this case 0+333a will become 333.

    if you're data is more complex, you may need to do more complicated things involving string manipulations through regexp or substring(whatever,locate(...)
    Last edited by bobert123; May 29th, 2013 at 05:27 PM.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by bobert123
    you can use implicit conversion like 0 + whatever
    this works only in mysql

    unfortunately, this isn't a mysql question -- the format of the error message and the use of the "dbo" owner are dead giveaways that this is microsoft sql server

    thread moved to the sql server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by geebra
    With the code below I get a error
    nope -- not that error from that query, because that query doesn't mention that column or value

    try again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    So if the query 'mention' that column(like add it to the SELECT), the error will be gone?

    BTW thanks for all the help Rudy
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    I seem to got the query working but now its not bringing in the time range that I want. It brings back data from 2009 and took about about 1 min to query.

    How can I refine this?

    Code:
    SELECT     dbo.meterDefineDPView.meterName, maxes.[Differential], dbo.hourlyMeterReading.contractTime, dbo.listDefine.listName
    FROM         dbo.meterDefineDPView INNER JOIN
                              (SELECT     meterID, Min(rawValue) AS [Differential]
                                FROM          dbo.hourlyMeterReading
                                WHERE      dataQuality IN ('10', '11') AND contractTime BETWEEN CONVERT(DATETIME, '2013-May-13', 102) AND CONVERT(DATETIME, 
                                                       '2013-May-14', 102)
                                GROUP BY meterId) maxes ON maxes.meterID = dbo.meterDefineDPView.meterId
    INNER JOIN
                          dbo.objectDefine ON dbo.meterDefineDPView.meterName = dbo.objectDefine.objectName INNER JOIN
                          dbo.listRefer ON dbo.objectDefine.objectId = dbo.listRefer.objectId INNER JOIN
                          dbo.hourlyMeterReading ON dbo.meterDefineDPView.meterId = dbo.hourlyMeterReading.meterId INNER JOIN
                          dbo.listDefine ON dbo.listRefer.listId = dbo.listDefine.listId
    WHERE     (dbo.listDefine.listName = 'orifice meter')

IMN logo majestic logo threadwatch logo seochat tools logo