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

Kate Juliff writes: -> -> ->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? This is an extremely useful feature. It allows you to execute very complex sql statements by using temp tables. Saving intermediate steps in temp tables can allow you to (1) increase performance, and (2) do things which are not possible in a single select. We use this feature all the time. ->2. Can you set a variable to the returned value of a select in ACE. You can do this in the format section of the report, but not in the select section of the report by assigning a previously selected value to a variable. Using temp tables and the features which often go unnoticed in select statements really do give you a lot of flexibility when writing ACE reports. ->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. This is where temp tables can help you. Your aggregate is probably not working the way you want because you are including all your columns in the select instead of only the ones involved. What you really want to do is something like this: ... select unique primary_key_column from temp1 into temp temp2 with no log; select sum (primary_key_column) sum_unique_key from temp2 into temp temp3 with no log; ... Hope this helps! Regards, - Cathy -------------------------------------------------------------------------------- Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (970) 491-1294 Colorado State University Veterinary Teaching Hospital Fax: (970) 491-1205