RE: AS400 Query Question

["Greg Novak, Momentum" <gnovak@xxxxxxxxxx>]

Nandu,

If you are having problems with Query or find that it either cannot do
what you want or there are too many steps to make it happen (multiple
linked queries).  You may want to look at Info WorkPlace (IWP).  IWP is
a report writing solution, available from Mapics, that can take the
effort out of creating reports.  When Query leaves you wanting, IWP is
just getting started...

Disclaimer: I work for Momentum Utilities, the developer of IWP.  Just
ask other IWP users on this list for opinions.  They are honest and very
vocal <g>.

Regards,
-Greg

Gregory Novak
Manager North American Technical Services
Momentum Utilities Pty Ltd
(630) 985-1736
gnovak@xxxxxxxxxx
www.jacana.com


-----Original Message-----
From: Nandagopal Padmanabhan [mailto:npadmanabhan@xxxxxxxxxxxxxx] 
Sent: Friday, April 11, 2003 12:58 PM
To: mapics-l@xxxxxxxxxxxx
Subject: AS400 Query Question


Hello Group,
I'm new to MAPICS and AS400 queries, but do have experience writing SQL
queries. I've been working on our MAPICS implementation for the last 4
months and this is the first time I'm writing AS400 queries. Basically,
I'm having trouble trying to incorporate GROUP BY aggregate function in
an AS400 query. I guess I haven't defined my report summary functions
and report breaks the right way. I would like to query ITEMBL file to
get a total on-hand inventory value for all warehouses. The SQL version
of this would look like this:

SELECT HOUSE, Sum(AVCST* MOHTQ) AS INVENTORY_VALUE
FROM ITEMBL
GROUP BY HOUSE

HOUSE = Warehouse
AVCST = Avg Unit Cost
MOHTQ = On Hand Total Quantity

I would like my report to look like this:

Warehouse       INVENTORY_VALUE
PBC             $107,117.41
PBD             $157,116.41
PBX             $257,116.41

Any suggestions on how to accomplish the above will be appreciated.
Could you also point me towards good online documentation/books on AS400
queries.

Thanks,
Nandu
Nandagopal Padmanabhan | Vestas-AWT
(503) 327-2050 | npadmanabhan@xxxxxxxxxxxxxx