RE: AS400 Query Question

[lisa.thomas@xxxxxxxxxxx]

Nandu,
Stepped out to a meeting but looks like you have gotten the answer.
Query/400 has been the tool traditionally used so old habits die hard.  It's
something I've really had to work on since I've worked on midrange systems
20+ years.  In fact the shop I'm now in was new to the iSeries 3 years ago
and I took the approach that I would not teach anyone how to use Query/400.
Browser has been our tool of choice for querying Mapics data.  I use SQL
exclusively in any new programs that I write.  The Microsoft ODBC
connections work well also but I only use this method for files that I have
created and put in a separate library due to security risks.  I would not
recommend this for Mapics files.

Glad you started this!  Have a great weekend!
Lisa
-----Original Message-----
From: Nandagopal Padmanabhan [mailto:npadmanabhan@xxxxxxxxxxxxxx]
Sent: Friday, April 11, 2003 4:12 PM
To: MAPICS ERP System Discussion
Subject: RE: AS400 Query Question


Lisa,
I tried STRSQL and it worked just fine. This makes me wonder why lot of
people use Query/400!! Is there any advantage that Query/400 has over AS400
SQL?

Thanks,
Nandu

-----Original Message-----
From: lisa.thomas@xxxxxxxxxxx [mailto:lisa.thomas@xxxxxxxxxxx]
Sent: Friday, April 11, 2003 12:01 PM
To: mapics-l@xxxxxxxxxxxx
Subject: RE: AS400 Query Question


By using STRSQL (AS400 SQL) you can run the SQL statement below.

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