IDS & ODBC & Excel: wrong data type of sum()

Hi, we use the informix product in a german language environment, where the decimal point is ",". Now we have found a special and inexplicable "error" when we use ODBC in Excel with IDS 11.50.UC4E. For example there is a table and a stored function in the database that was created with CREATE TABLE tb1 ((a interger , b MONEY(14,2) ) ; CREATE FUNCTION f1( m MONEY(14,2) RETURNING MONEY(14,2);RETURN m; END FUNCTION; When we execute SQL queries in Excel from VBA macros like this: Set m_rstDsn = DBEngine.Workspaces(0).OpenDatabase( _ "database", dbDriverNoPrompt, False, "ODBC;").OpenRecordset( _ "Select a ,sum(b) from tb1 group by 1 ", _ dbOpenSnapshot, dbSQLPassThrough) the data type of the sum(b)-column is text, and a further numeric processing is not possible! First we have thought that this is a configuration problem of the environment of Excel, the odbc driver or the database engine. But the problem does not occur - when we use another informix database engine (Informix SE) - in adhoc querys (with Excel) - or when the SQL of the example is modified like "Select a ,f1(sum(b)) from tb1 group by 1" We have already tried two different versions of the informix ODBC driver with the same result. At the moment we have no idea which component (Excel, odbc, db engine) causes this strange problem. Any ideas out there? Regards K.S.Faszl

The aggregate function SUM() should be returning a DECIMAL type which is numeric and the ODBC driver should be converting that to an ODBC NUMERIC type. It is odd that passing the results throught the f1() function resolves the issue. Have you tried using a cast: SELECT a, SUM(b)::MONEY(14,2) FROM tb1 GROUP BY 1; That might resolve the issue without the overhead of the function call, though it does not resolve the issue of "why" this is happening. Have you opened a case with IBM? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 27, 2012 at 11:59 AM, KLAUS FASZL <faszl@uhde.de> wrote: > Hi, > > we use the informix product in a german language environment, where the > decimal point is ",". > > Now we have found a special and inexplicable "error" when we use ODBC in > Excel > with IDS 11.50.UC4E. > > For example there is a table and a stored function in the database that was > created with > > CREATE TABLE tb1 ((a interger , b MONEY(14,2) ) ; > CREATE FUNCTION f1( m MONEY(14,2) RETURNING MONEY(14,2);> RETURN m; > END FUNCTION; > > When we execute SQL queries in Excel from VBA macros like this: > > Set m_rstDsn = DBEngine.Workspaces(0).OpenDatabase( _ > > "database", dbDriverNoPrompt, False, "ODBC;").OpenRecordset( _ > > "Select a ,sum(b) from tb1 group by 1 ", _ > > dbOpenSnapshot, dbSQLPassThrough) > > the data type of the sum(b)-column is text, and a further numeric > processing > is not possible! > > First we have thought that this is a configuration problem of the > environment > of Excel, the odbc driver or the database engine. > > But the problem does not occur > - when we use another informix database engine (Informix SE) > - in adhoc querys (with Excel) > - or when the SQL of the example is modified like > "Select a ,f1(sum(b)) from tb1 group by 1" > > We have already tried two different versions of the informix ODBC driver > with > the same result. > > At the moment we have no idea which component (Excel, odbc, db engine) > causes > this strange problem. > > Any ideas out there? > > Regards > K.S.Faszl > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec5299ff3767e3f04b9f55893

Hi Art, thanks for the better workarround with the cast operator. But it would be very hard to adapt a lot of queries in different Excel files at many customer sites! This is not applicable for us. I conclude from your answer that you assume that the wrong data type that is returned from the aggregate function sum() is a bug in the database engine. I aggree with you because the wrong data type don't occur with some other aggregate functions (max(), min()). Regards K.S. Faszl