SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Syntax error converting the varchar value '1.5' to a column of data type int.

    hello,
    i have this trigger that acts as a billing system for telephone numbers inserted into my db.

    Code:
    declare @results int
    select @results = 
    case when receipient like '234804%' or receipient like '1%' or receipient like '44%'
    then convert(int,'1.5')
    else convert(int,'1')
    end
    from inserted
    
    begin
    
    update sms_credits set credits = (@total_credits -  @results)
    end
    however i get this error message

    Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '1.5' to a column of data type int.


    Where am i wrong here

    thanks
    afrika

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why are you trying to convert 1.5 to an integer?

    you're going to get 2 (rounded up) so why not just use 2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks Rudy,
    however the billing system is per credit sent and for the countries of the above format we need to use 1.5

    Is there anyway of getting a fraction in integer format ?

    thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no

    an integer is a whole number, decimal portions are not allowed

    why don't you declare @results as decimal(11,2), and instead of converting a string to a decimal number, why not just assign a numeric literal?
    Code:
    declare @results decimal(11,2)
    select @results = 
    case when ...
    then 1.5
    else 1.0
    end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am also trying to use this code
    convert(int,convert(decimal(18,4),'1.5'))

    but it still appears to round it up to 1

    any advice ?
    Afrika

  6. #6
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi Rudy,
    its deducting 2 instead of 1.5 from the database.

    here is my code


    declare @results decimal(11,2), @total_credits numeric
    select @results =
    case when receipient like '234804%' or receipient like '1%' or receipient like '44%'
    then '1.5'
    else '1'
    end
    from inserted

    begin

    update sms_credits set credits = (@total_credits - @results)
    end
    how could i achieve this deduction of 1.5 credits

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    any advice? yes, i've already suggested it

    integers are whole numbers, so if you want to use 1.5, you can't use integer

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    also, please note: 1.5 is a number, '1.5' is a string (character datatype)

    there's no point converting a string to a number, just use the number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Amazing, what we learn everyday.
    Didnt realize the difference

    would try that just now.

    Thanks a lot Rudy
    afrika

  10. #10
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi Rudy,
    it still deducts 2 instead of 1.
    I changed the datatype of my db, to numeric

    And my code stil remains as above

    Any advice
    rgds

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    nowhere do you actually assign a value to @total_credits numeric

    please show exact latest version of your code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    declare @results as decimal(11,2) , @total_credits numeric
    select @total_credits = (select credits from inserted)

    select @results =
    case when receipient like '234804%' or receipient like '1%' or receipient like '44%'
    then 1.5
    else 1.5
    end
    from inserted

    begin


    update sms_credits set credits = (@total_credits - @results)


    end
    thanks

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you forgot to specify the number of decimal places for @total_credits, so it rounds to the nearest integer (the default is 0 decimal positions)

    by the way, if you assign 1.5 in both the THEN and the ELSE clauses, then you might as well not bother with the CASE at all

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry i specified 1.5 and 1, but when errors, i was trying to debug and changed both to 1.5

    i have changed them back now
    how do i specify the amt of decimal places ?

    thanks a lot for your assistance

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the time has come for me to gently suggest that you should really try to find these answers yourself

    how to specify the number of decimal places for a numeric field is very adequately covered in Books OnLine

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ha ha ha

    Thanks a lot Rudy

    i really do appreciate your assistance.
    have a pleasant week ahead
    Afrika


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •