Re: Multiple selects, group by's variables etc in ACE
In article <kcjDCEG3r.BuI@netcom.com> kcj@netcom.com "Kate Juliff" writes:
> 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?
The first selects PREpare data by creating temporary tables which are
used in the final select to present data to the program. This can
sort the problem when the data required can't be retrieved by a
single SQL statement, or where to do so would create horrendous
performance problems.
> 2. Can you set a variable to the returned value of a select in ACE.
Presumable this means 'to one of the values in a row returned by
a SELECT'. Yes:
database mydb
end
define
variable totsize integer
end
select * from systables
end
format
on every row
let totsize = nrows * rowsize
print tabname, totsuze
end
Of course, this is trivial and the SELECT could do the multiplication
but it illustrates the point.
> 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.
select * from yourtable
order by id, item
format
after group of item
let myvalue = myvalue + total
--
============================================================================
Sally Woolrich | This mail contains my personal
sally@excelsis.demon.co.uk | views not those of my employer!
============================================================================