RE: AS400 Query Question

[jleroi@xxxxxxxxx]

Nandu,

As you can see there are very many ways to solve this. Since the destination
of a lot of my work ends up being Excel, I use Micrsoft query from within
Excel along with an IBM Client Access ODBC connection to my MAPICS files;
any AS/400 ODBC will work. 
 
I have been using SQL and Microsofts Query Wizard for years to extract data
to either WExcel or Access in order to produce reports with better graphs,
pivot tables or just to provide raw data to an Excel Power user so they can
slice and dice to their hearts content.

Good Luck finding what works best for you and let me know if you need any
assistance with setting up ODBC connections.

Regards,
Jon Le Roi
Director of Information Systems
Tegal Corporation

-----Original Message-----
From: Nandagopal Padmanabhan [mailto:npadmanabhan@xxxxxxxxxxxxxx]
Sent: Friday, April 11, 2003 10:58 AM
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



_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.