RE: AS400 Query Question

Nandagopal,

Have you tried file transfer (if you have Client Access) to set up a
structured query?   You can write your SQL with that.  You would select Data
Transfer from AS/400 and after selecting the file(s), click Data Options,
click enable group functions and select Details... go from there in setting
up your query selections.  I don't know SQL very well, but used your example
and got exactly the output you are expecting.  You can select display, file,
or printer for your file transfer output.

Ann Neal
LeTourneau, Inc.

-----Original Message-----
From: mapics-l-bounces@xxxxxxxxxxxx
[mailto:mapics-l-bounces@xxxxxxxxxxxx]On Behalf Of Nandagopal
Padmanabhan
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



_______________________________________________
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.