Stored Procedure with Group By

Today I reported the following problem to the Informix Technical Support. If you have any comments about it please reply to the Newsgroup. (Maybe it's not a problem and I misunderstood something, in that case let me know...) Any comments are well appreciated. Problem nr 770569 ---------------------- We have written a stored procedure called DIVIDE(X,Y) that returns an argument like an ordinary division X/Y except if Y is 0 or NULL. If Y is 0 or NULL the procedure will return the value NULL instead. This makes it very easy to generate sql querys that will always return a result even if the one of the denominators in the query is 0 or NULL. Otherwise that will be a really tedious task. ( Example: create table kalle as select a1/(a2/a3+a4) as x1 ,a5/(a6/a7+a8) as x2 from tabell2;) To generate this query in a way that will always produce a result even if one of the denominators is NULL or 0 will be _MUCH_ easier if you have a function like DIVIDE. ( If you don't believe me then please try -. The result table I would like would have NULL in each place where any denominator was 0 or NULL. ALL rows must be in the result table - even if the denominator was 0 or NULL for any of the rows.) PROBLEM: --------------- The function DIVIDE works fine except if you also have a GROUP BY clause in the query. It seems that you cannot write an expression like: create table TABELL1 as select DIVIDE(SUM(X),SUM(Y)), Z from TABELL0 group by Z;It seems that a stored procedure cannot operate on aggregated/summarized fields!!! I read some note (in a Informix user group) that this was possible in version 7.2 but is prohibited in version 7.3 because the procedure _might_ return more than one row and the expression is evaluated in prepare time (instead of runtime) in version 7.3. QUESTION No 1 *Is it in any way (by options or whatever) possible to have a stored procedure that operates on summarized/aggregated columns in version 7.3? QUESTION No 2 *If not, WILL it be possible (in a later release or by some patch)? If the answer to both questions are NO I would strongly suggest that you consider the problem we have. I am certain that this can't be an uncommon problem. You have a query with a lot of divisions and would like to get a result without having to write an amount of SQL . Try this one: create table kalle as select sum(a1)/(sum(a2)/sum(a3)+sum(a4)) as x1 ,sum(a5)/(sum(a6)/sum(a7)+sum(a8)) asx2 a15 from tabell2 group by a15; The result table I would like would have NULL in each place where any denominator was 0 or NULL. ALL rows must be in the result table - even if the denominator was 0 or NULL for any of the rows QUESTION No 3 *If your answer is NO to both previous questions, I would like you to specify how I would get the result table I'm after without having to write an amount of SQL that could fill a book. QUESTION No 4 *Maybe you know some other way of solving the problem I have (= question no 3) (that does not involve writing a LOT of SQL) Maybe it works if you make subselects and then use a master select with the DIVIDE function but it seems a very clumpsy way to solve the problem. This problem is VERY important to us and might decide if we choose Informix, Oracle or some other database for the software products we build. Maybe I overlooked some simple solution. Please let me know. /Masse PS I am using a software called SAS which can deliver a NULL value if there is division by 0 or NULL. -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum