RE: Embedded SQL

Douglas,

It sounds like you just need to sort your fields by invoice location and
margin pct using the ORDER BY clause. Using the GROUP BY clause allows you
to consolidate multiple records into a single record. An example would be
reporting totals by invoice location. If you need to report each invoice,
then GROUP BY is not the way to go. FYI - with embedded SQL, performing a
page break after a change in location would be the responsibility of your
program.

Hope this helps!
Richard



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Douglas W. Palme
Sent: Monday, December 12, 2005 2:50 PM
To: Mid Range List
Subject: Embedded SQL


Why does the system force me to include all fields in a group by statement?

I am pulling several fields including two calculated fields (margin and
margin pct), but by grouping by multiple fields I am not going to see
everything from low to high on the pct.

As an example, I am pulling the following fields:

invoice number
invoice location
sold to number
gl number (revenue type)
gl desc
extended price
extended cost
margin (calculated)
margin pct (calculated)

I need to group them by the inovice location so that the report does a page
break after a change in the location, I need to rank them from low to high
on the pct.

When I ran the query in STRSQL, it runs extremely fast (across a logical) up
until I add in the group statement and then it required that I add in all
seven fields (with the exception of the calculated fields)....which then
throws off my grouping.....

Any suggestions ?????