Group by on Select_SQL

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
bweeks
Posts: 3
Joined: Tue Apr 05, 2016 9:41 am

Group by on Select_SQL

Post by bweeks » Fri Jun 03, 2016 3:54 am

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
Posts: 10
Joined: Thu May 26, 2016 8:46 am

Re: Group by on Select_SQL

Post by Tim McEntee » Mon Jun 06, 2016 9:36 am

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

Post Reply