Re: Result precision and scale for decimal arithmetic expression and aggregate function

Decimals are different from floating point numbers. Decimals are still considered a precise data type like integers, so the scale is important. I'd like to have a fomula of the precision and scale of the result types. Thanks! Fernando Nunes <spam@domus.online.pt> wrote in message news:<bdkkd0$sc70v$1@ID-161111.news.dfncis.de>... > Lan Huang wrote: > > I'd like to know what's the result precision and scale of the decimal > > arithmentic expression and aggregate function. > > > > For example, what's the result type of > > decimal(p1, s1)+decimal(p2, s2) > > decimal(p1, s1)-decimal(p2, s2) > > decimal(p1, s1)*decimal(p2, s2) > > decimal(p1, s1)/decimal(p2, s2) > > AVG(decimal(p1, s1)) > > etc > > > > It will also be nice if someone tell me which documentation describes > > the result types of the expressions and functions. > > > > Thanks, > > Lan > > Don't take this as a definitive answer but: > > - The precision of any floating point data type can vary with the > architecture. The are numbers wich cannot be represented exactly in some > standard representations. > > You can find some information in the SQL Reference Guide > > Regards

Lan Huang wrote: > Decimals are different from floating point numbers. > Decimals are still considered a precise data type like integers, so > the scale is important. I'd like to have a fomula of the precision and > scale of the result types. Generally, if it is an aggregate, the answer is a DECIMAL(32) - a 32-digit floating point value. If it is an arithmetic operation, you get whatever you get (it will be big enough to hold your answer - that's about the only guarantee I'd make, and even that has some caveats attached). As Richard Harnden said, you can verify or contradict this observation by getting hold of SQLCMD (IIUG Software Archive) and running your choice of aggregates and arithmetic via SELECT statements with the -H option set on the command line, or by saying 'headings on'. It will tell you what the results are. > Fernando Nunes <spam@domus.online.pt> wrote: >>Lan Huang wrote: >> >>>I'd like to know what's the result precision and scale of the decimal >>>arithmentic expression and aggregate function. >>> >>>For example, what's the result type of >>>decimal(p1, s1)+decimal(p2, s2) >>>decimal(p1, s1)-decimal(p2, s2) >>>decimal(p1, s1)*decimal(p2, s2) >>>decimal(p1, s1)/decimal(p2, s2) >>>AVG(decimal(p1, s1)) >>>etc >>> >>>It will also be nice if someone tell me which documentation describes >>>the result types of the expressions and functions. >> >>Don't take this as a definitive answer but: >> >>- The precision of any floating point data type can vary with the >>architecture. The are numbers wich cannot be represented exactly in some >>standard representations. >> >>You can find some information in the SQL Reference Guide I don't think there is an official statement about such results, not least because within the timespan of recorded history, the answer has changed - older servers will give different results from newer ones. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/