Re: AS400 Query Question

[Rusty Jackson <rustyjackson@xxxxxxxxxxx>]

Jon:

I am a third party consultant/programmer that works in a lot of MAPICS
accounts, small and large.  I constantly run into situations where your
solution with ODBC and Microsoft products is exactly what the client needs
to use.  However, more often than not, the end-users in small companies
without in-house IS expertise do not have any idea how to go about setting
up a ODBC connection and then using it with their Microsoft products,
Access/MS QUERY/Excel/etc.

Rusty Jackson
Rusty Jackson And Associates
P.O. Box 20193
Knoxville, TN  37940

----- Original Message ----- 
From: <jleroi@xxxxxxxxx>
To: <mapics-l@xxxxxxxxxxxx>
Sent: Friday, April 11, 2003 4:19 PM
Subject: RE: AS400 Query Question


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