RE: AS400 Query Question

Nandagopal,

I agree with Dale. I am using SQLThing (www.SQLThing.com) and Access to do
certain SQL queries against the MAPICS files, even though I started with
Query/400. (Still use Query/400, of course.) Good luck.

         -- Joan

Joan McCready, IS Manager      phone:  636-479-4499
MetalTek International         fax:  636-479-3399
The Carondelet Division        www.metaltekint.com
8600 Commercial Blvd
Pevely, MO  63070                      


-----Original Message-----
From: DaleGindlesperger@xxxxxxxxxxx [mailto:DaleGindlesperger@xxxxxxxxxxx] 
Sent: Friday, April 11, 2003 12:07 PM
To: MAPICS ERP System Discussion
Subject: Re: AS400 Query Question



Nandagopal,

You will be quite disappointed in how limited Query/400 is if you are used
to SQL.  I will do this query for you and e-mail the print definition
directly to your listed e-mail account.  It's actually a very simple query
(easy for me to say!)

I would recommend looking at NGS-IQ as a supplement to using Query/400 -
it's what I feel that Query/400 should have "grown up" into.  And no, I
don't work for NGS, I just am a user that uses both.

Dale Gindlesperger
IT Manager
Fleetwood Folding Trailers, Inc.


 

                      "Nandagopal

                      Padmanabhan"              To:
<mapics-l@xxxxxxxxxxxx>

                      <npadmanabhan@xxxx        cc:

                      as-awt.com>               Subject:  AS400 Query
Question                                                          
                      Sent by:

                      mapics-l-bounces@x

                      idrange.com

 

 

                      04/11/2003 01:57

                      PM

                      Please respond to

                      MAPICS ERP System

                      Discussion

 

 





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.





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