|
midrange.com
Nandu, I hit some print keys to give you an idea. Specify File Selections File
. . . . . . . . . ITEMBL
Library . . . . . . AMFLIB
Member . . . . . . . . *FIRST
Format . . . . . . . . ITEMBLMC Define Result Fields Field
Expression Column Heading
DOLL avcst*mohtq Inventory
ValueSelect and
SEquence
10 HOUSE Warehouse 3
20 DOLL avcst*mohtq 29 11
Select Sort FieldsSort
Prty A/D Field Text
10 A HOUSE Warehouse
DOLL avcst*mohtq Select
Report Summary Functions
Type options, press Enter.
1=Total 2=Average 3=Minimum 4=Maximum 5=Count
---Options--- Field Text
HOUSE Warehouse
1 DOLL avcst*mohtq
Define Report Breaks
Type break level (1-6) for up to 9 field names, press Enter.
(Use as many fields as needed for each break level.)
Break Sort
Level Prty Field Text
1 10 HOUSE Warehouse
DOLL avcst*mohtq
Select Output Type and Output Form
Type choices, press Enter.
Output type . . . . . . . . . . . 1 1=Display
2=Printer
3=Database file
Form of output . . . . . . . . . . 2 1=Detail
2=Summary only
Line wrapping . . . . . . . . . . N Y=Yes, N=No
Wrapping width . . . . . . . . . Blank, 1-378
Record on one page . . . . . . . N Y=Yes, N=No Hope this
helpsPete Olshavsky
Nandagopal Padmanabhan <npadmanabhan@xxxxxxxxxxxxxx> wrote: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.
---------------------------------
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
