Re: Multiple selects, group by's variables etc in ACE

I saw one answer to this question which started off answering item 1 with "I don't know either". I think one answer to item 1 is "to solve item 3"! >From: kcj@netcom.com (Kate Juliff) >Date: Fri, 28 Jul 1995 00:02:14 GMT >X-Informix-List-Id: <news.15792> > >I have a few questions. > >1: What is the point of being able to do a number of selects in writing >an ACE report if only the last one has any effect? You must create temp tables with all the SELECT statements except the last. It is up to you what you do with those temporary tables, but the normal reason for creating them is so that you can use them in the final SELECT. >2. Can you set a variable to the returned value of a select in ACE. All the values selected are in implicitly defined variables. If you want to place it in an explicitly defined variable, use: LET explicit = implicit >3. I have a table > >id item val1 val2 total > >with the following sort of rows > >1 2 4.1 6.2 6 >1 2 5.1 7.2 6 >1 2 9.1 6.2 6 >1 3 7.1 6.1 2 >1 3 0.2 5.1 2 >2 1 8.1 9.0 3 > >The final column value are totals of some qty of all value of the rows >with the same primary key (which is columns 1 and 2 combined. So that the >6 applies to all three rows that have 1, 2 as the primary key. > >And I want to total all values for each primary key and each group of >rows having the same value in column 1. However, I don't want to count >the "6", for example, more than once. > >If I use group by, I get 22 for the sum of all of group by id. I WANT to >get 8! Even if I don't have a "group by item". I realise I can get round >this by setting up variables and resetting them on change of groups, but >it's messy. I tried setting a variable in the outer "group by" clause and >summing the contents by group total of variable, but that didn't work. I have a number of fairly serious questions about this sample data such as: * Why is the database designed with this denormalized data structure? * How come the primary key is (id, item) but there are multiple rows with the same PK, which means that the PK is not really the PK. However, ignoring these issues, one way of dealing with this is to use multiple select statements in your report! Whether it is the best way is another matter altogether, and it undoubtedly isn't the only way, but ... SELECT UNIQUE Id, Item, Total FROM Sometable INTO TEMP TempTable1; SELECT Id, SUM(Total) Id_total FROM TempTable1 GROUP BY Id INTO TEMP TempTable2; SELECT S.id, S.Item, S.Val1, S.Val2, T.Id_total FROM SomeTable S, TempTable2 T WHERE S.Id = T.Id END Now you can print the id_total in the AFTER GROUP OF clause... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>