RE: [firebird-support] numeric value is out of range
Thanks for your reaction Karol, The exact message given by IBexpert is : can't format message 13:198 -- message file C:\Windows\firebird.msg not found. arithmetic exception, numeric overflow, or string truncation. numeric value is out of range. The error also occurs in our Delphi application with IBO components where the message error is the same : ISC ERROR CODE : 335544321 Arithmetic exception, numeric overflow, or string truncation Numeric value is out of range. At procedure ‘RESULTATS_VTEFACT’ line: 349, col: 9 At that line is the code CA_BRUT_HT = :CA_BRUT_TTC_fact / :TAUX_TVA; where the three variables are defined as numeric (18,8). The error occurs only when the computed result has too much digits. I’ll try with flame robin, but I feel that I’ll have the same result (as with ibocomponent I also have it). De : firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Envoyé : jeudi 30 octobre 2014 09:58 À : firebird-support@yahoogroups.com Objet : Odp: [firebird-support] numeric value is out of range Hi, Did you run this outside ibexpert? What is exactly error message and code? Try it with flamerobin or fbsql. And then post info. Regards, Karol Bieniaszewski - Reply message - Od: caste...@titelive.be [firebird-support]mailto:caste...@titelive.be%20[firebird-support] firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com Temat: [firebird-support] numeric value is out of range Data: czw., paź 30, 2014 09:14 Hi all, I try to figure out why i have a problem in my database since i convert it from firebird 1.5 (dialect 1, no charset ) to firebird 2.5 (dialect 3 and defined charset to utf8). The following SQL failed in the new database (fb 2.5 dialect 3). It worked in fb 1.5 (dialect 1). Even if i try to cast the result in a numeric value. --- That's what i do not understand. I can understand that a numeric divided by another numeric can give a float... but why the cast does'nt work ? SELECT cast( cast(8975.00038147 as numeric(18,8)) / cast(1.06 as numeric(18,8)) as numeric(18,8)) FROM RDB$DATABASE notes : - the values 8975.0038147 come from is a field value. field definition is float. - the problem that i actually have occured in a procedure where there is a computed variable. CA_BRUT_HT = :CA_BRUT_TTC_fact / :TAUX_TVA; where the three variables are defined as numeric (18,8). But ibexpert show me the SQL that give the problem (the select i pasted in this mail). What's the best to do ? working with float variables ? Thanks in advance for the light you can give me with that problem. Best regards, Marianne
Re: [firebird-support] numeric value is out of range
The definition of numeric calculation was changed between dialect 1 and 3 with Interbase 6. This is described in the Interbase 6 getting started (available from the website). Basically in dialect 1 this type of division overflowed into double precision, it doesn't in dialect 3 and your result doesn't fit and it raises an arithmetic overflow. Mark - Reply message - Van: caste...@titelive.be [firebird-support] firebird-support@yahoogroups.com Aan: firebird-support@yahoogroups.com Onderwerp: [firebird-support] numeric value is out of range Datum: do, okt. 30, 2014 09:14 Hi all, I try to figure out why i have a problem in my database since i convert it from firebird 1.5 (dialect 1, no charset ) to firebird 2.5 (dialect 3 and defined charset to utf8). The following SQL failed in the new database (fb 2.5 dialect 3). It worked in fb 1.5 (dialect 1). Even if i try to cast the result in a numeric value. --- That's what i do not understand.I can understand that a numeric divided by another numeric can give a float... but why the cast does'nt work ? SELECTcast(cast(8975.00038147 as numeric(18,8)) / cast(1.06 as numeric(18,8)) as numeric(18,8))FROM RDB$DATABASE notes : - the values 8975.0038147 come from is a field value. field definition is float.- the problem that i actually have occured in a procedure where there is a computed variable.CA_BRUT_HT = :CA_BRUT_TTC_fact / :TAUX_TVA; where the three variables are defined as numeric (18,8).But ibexpert show me the SQL that give the problem (the select i pasted in this mail). What's the best to do ? working with float variables ? Thanks in advance for the light you can give me with that problem. Best regards,Marianne
RE: [firebird-support] numeric value is out of range
Thanks Mark. I’ll read the documentation again (for this). At migration time, I saw that we didn’t have fields with numeric definition… and i passed that section. But i didn’t noticed that we had “numeric” in our stored procedures…. And I fall on it now. Great thanks ! I go back to documentation ☺ Best regards, Marianne De : firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Envoyé : jeudi 30 octobre 2014 10:07 À : firebird-support@yahoogroups.com Objet : Re: [firebird-support] numeric value is out of range The definition of numeric calculation was changed between dialect 1 and 3 with Interbase 6. This is described in the Interbase 6 getting started (available from the website). Basically in dialect 1 this type of division overflowed into double precision, it doesn't in dialect 3 and your result doesn't fit and it raises an arithmetic overflow. Mark - Reply message - Van: caste...@titelive.be [firebird-support]mailto:caste...@titelive.be%20[firebird-support] firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com Aan: firebird-support@yahoogroups.commailto:firebird-support@yahoogroups.com Onderwerp: [firebird-support] numeric value is out of range Datum: do, okt. 30, 2014 09:14 Hi all, I try to figure out why i have a problem in my database since i convert it from firebird 1.5 (dialect 1, no charset ) to firebird 2.5 (dialect 3 and defined charset to utf8). The following SQL failed in the new database (fb 2.5 dialect 3). It worked in fb 1.5 (dialect 1). Even if i try to cast the result in a numeric value. --- That's what i do not understand. I can understand that a numeric divided by another numeric can give a float... but why the cast does'nt work ? SELECT cast( cast(8975.00038147 as numeric(18,8)) / cast(1.06 as numeric(18,8)) as numeric(18,8)) FROM RDB$DATABASE notes : - the values 8975.0038147 come from is a field value. field definition is float. p- the problem that i actually have occured in a procedure where there is a computed variable. CA_BRUT_HT = :CA_BRUT_TTC_fact / :TAUX_TVA; where the three variables are defined as numeric (18,8). But ibexpert show me the SQL that give the problem (the select i pasted in this mail). What's the best to do ? working with float variables ? Thanks in advance for the light you can give me with that problem. Best regards, Marianne