Re: Betterment of AS/400 - Query/400

On 02/27/99 05:03:11 PM Mark Lazarus  wrote:

> SQL/400 output formatting stinks.  The prompter is also the pits.  It
>requires far too much knowledge of SQL syntax and barely helps you out. It
>also doesn't doesn't make it easy to run in batch.  It doesn't allow
>totaling on a function (i.e. getting a final total of SUM( AMT ) ) or
>assigning work variables.  I can go on and on.  IMHO, SQL/400 will never
>get those features because IBM tends to stick (close) to the ANSI standard
>for SQL. 

You could always print final totals:

select name,zip,amount,1 from tablename union 
select 'Final Total',0,sum(amount),9 from tablename 
order by 3

The trick with UNION is to match the columns between the upper SELECT and 
the lower SELECT.

name --- 'Final Total'        character
zip --- 0                                   numeric
amount --- sum(amount)   numeric
1 --- 9                                       numeric

The zero in the lower select is a place holder - there's no logical match 
for zip in the total record.
The last column is used only to sort the total after all the detail 
records.

I typically think of SQL as a programmer tool, and Query as an end-user 
power tool.  I don't encourage end-users to use Query for "production" 
work; when the power user is out and there's nobody else in the department 
who can run "Fred's report" you're left scrambling to try to figure out 
which of Fred's 300 queries he runs every week, etc.

Buck Calabro
CommSoft, Albany, NY
mailto:mcalabro@commsoft.net
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---