Group by on Select_SQL

[bweeks]

I have a file with sales by salesman number and amount. I'm trying to produce a list of
the top salesmans for a given day. Most salesman have multiple entries.

In SQL I would code it as:

Select SMN,sum(amt)
from Sales
Group by SMN
Order by sum(amt) desc

How would I code my Select_SQL to Group by SMN field, summing AMT field ?

[Tim McEntee]

if you are putting the SQL in the using clause then you would specify a field in fields part of the Select_sql that matched the type of the field in the sql statement. If you can't get the fields to match then use the cast as statement in side the SQL.

eg
#SQLSTR := 'select SMN, cast(sum(amt) as dec(9, 2))'
#SQLSTR += ' from Sales'
#SQLSTR += ' Group by SMN'
#SQLSTR += ' Order by sum(amt) desc'

define field(#totalfld) type(*DEC) LENGTH(9) decimals(2)

select_sql fields(#SMN #totalfld) using(#SQLSTR)
....
endselect