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

    Join Date
    Mar 2010
    Posts
    3
    Rep Power
    0

    Smile Overflow occurred during data type conversion. Conversion error from string "$$$###"


    The following queries result in the error - Overflow occurred during data type conversion. Conversion error from string "$$$###".

    when running this in EMS Manager Lite or inside a delphi/report builder application.

    Select
    tbl1.FIRSTNAME,
    tbl1.Image2,
    Sum(FieldA) as TotalFieldA
    from Table1 tbl1
    inner join Table2 tbl2
    on tbl1.REG = tbl2.REF
    Group By tbl1.FirstName, tbl1.IMAGE2

    If the references to the IMAGE2 field is removed in the select and the group by then the query is fine. So is there some sort of issue where an Image (BLOB field) cannot be selected in a SQL Statement with a summing function. The Standard Select * from Table1 shows the image field no problem.

    FYI - using Delphi 7 and Firebird 1.5.2
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    388
    I don't see how you can do a GROUP BY on a BLOB field.
    I'm quite surprised that Firebird actually accepts the SQL.

    I have not double checked, so I could be wrong

    Clive
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    3
    Rep Power
    0
    Originally Posted by clivew
    I don't see how you can do a GROUP BY on a BLOB field.
    I'm quite surprised that Firebird actually accepts the SQL.

    I have not double checked, so I could be wrong

    Clive

    Your most likely correct, which is why its failing, i guess i need to devise a different way to achieve what i need which is a group of total fields from a range of records in a table with one image as like a logo for that information.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    3
    Rep Power
    0

    I think this is my answer to this problem


    Firebird subquery format is done a little different but using this method i can do the following which makes for lengthing SQL.

    Select
    tbl1.Reference
    tbl1.FIRSTNAME,
    tbl1.Image2,
    (Select Sum(FieldA)
    from Table2 TBL2
    where tbl2.Reference = tbl1.Reference) as TotalFieldA
    from Table1 tbl1

    This eliminates the link between tbl1 & tbl2 and also there is no need to Group by because there is no FUNCTION in the main select statement. So I get the resulting row of:
    Reference : Firstname : Image2 : TotalFieldA in one line.
  8. #5
  9. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    This is usually faster on big tables (don't know if that affects you)
    Code:
    select
     tbl1.Reference
     tbl1.FIRSTNAME, 
     tbl1.Image2,
     tbl2.total_field_A
    from
     table1 tbl1
    inner join
    (
     select
      tbl2.reference,
      sum(tbl2.fieldA) total_field_A
     from
      table2 tbl2
     group by
      tbl2.reference
    ) tbl2
    on 
     tbl1.reference = tbl2.reference

    Comments on this post

    • jevgenijsblaus agrees
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    1
    Rep Power
    0
    Guys, i've got query, very simple query -
    select code
    from codes
    where code <> 0

    and i got Overflow occurred during data type conversion. Conversion error from string "A"

    The problem was, that i compared code field with 0 Integer, when i needed to use '0' - string!
    Maybe this will help you or somebody else. Check your types!
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    11
    Try to use function "CAST()" see examples in Firebird: FAQ

IMN logo majestic logo threadwatch logo seochat tools logo