RE: AS400 Query Question

[lisa.thomas@xxxxxxxxxxx]

No but you can use F13 to change the session attributes to 2 for the Select
output destination which will produce a spool file of the data.

                          Change Session Attributes

 

Type choices, press Enter.

 

  Statement processing . . . . .   *RUN           *RUN, *VLD, *SYN

  SELECT output  . . . . . . . .   2              1=Display, 2=Printer

                                                  3=File

  Printer  . . . . . . . . . . .   *PRINT         Name

  Commitment control . . . . . .   *NONE          *NONE, *CHG, *CS, *ALL,
*RR  
                                                  *NC, *UR, *RS

  Date format  . . . . . . . . .   *MDY           *JOB, *USA, *ISO, *EUR,
*JIS 
                                                  *MDY, *DMY, *YMD, *JUL

  Date separator . . . . . . . .   '/'            *JOB, '/', '.', ',', '-'

                                                  ' ', *BLANK

  Time format  . . . . . . . . .   *HMS           *HMS, *USA, *ISO

                                                  *EUR, *JIS

  Time separator . . . . . . . .   ':'            *JOB, ':', '.', ','

                                                  ' ', *BLANK

  Data refresh . . . . . . . . .   *ALWAYS        *ALWAYS, *FORWARD

  Allow copy data  . . . . . . .   *YES           *YES, *OPTIMIZE, *NO

  Naming convention  . . . . . .   *SQL           *SYS, *SQL

 
More...  

-----Original Message-----
From: Ann Neal [mailto:aneal@xxxxxxxxxxxxxxxxxx]
Sent: Friday, April 11, 2003 3:17 PM
To: MAPICS ERP System Discussion
Subject: RE: AS400 Query Question


Lisa,

Is there a print statement after selecting the data?

Thanks,
Ann

-----Original Message-----
From: mapics-l-bounces@xxxxxxxxxxxx
[mailto:mapics-l-bounces@xxxxxxxxxxxx]On Behalf Of
lisa.thomas@xxxxxxxxxxx
Sent: Friday, April 11, 2003 2: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.