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

Kate, A few answers: 1. Sorry, I've never fathomed this one out either! 2. I am assuming you want to use the variable in a second ACE report: If the first report returns a single value, output it to a file, define the variable in the second report, prompt for it in the input section, and run the report from the command line as follows: sacego "report_name" < "filename" The second report will then read the value of the variable from the file. 3. If you "select unique id, total" in the report, the values in the total column will only be selected and counted once. HTH Cheers, Richard. ----------------------------------------------------------------- | _ /\\ | Richard Thomas | | \\ 0_ | r.thomas@csl.gov.uk | | \\ / | | | oo/ | "20 Regal and a four-pack, | | / \\ | I guess I'm set for the night" | | \\_/ | - "TV Tan" The Wildhearts | ----------------------------------------------------------------- ----- Begin Included Message ----- From ilist@rmy.emory.edu Fri Jul 28 10:24:43 1995 From: kcj@netcom.com (Kate Juliff) Subject: Multiple selects, group by's variables etc in ACE Date: Fri, 28 Jul 1995 00:02:14 GMT To: informix-list@rmy.emory.edu X-Informix-List-To: rt10hp@csl.gov.uk 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? 2. Can you set a variable to the returned value of a select in ACE. 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. ----- End Included Message -----