× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



The nice thing about using Client Access file transfer you can do the SQL
selections, save the whole definition, display/file(Excel, tab, etc./or
print(local print), and make it an icon and launch as needed... doesn't need
to open a green screen.  Nice desktop reporting.

We also entered SQL statements using STRSQL and saved them to source,
compiled, then called (or set to run on a schedule).  The only thing we ran
into with these is if the file is not journaled, the RPG did not work.  It
does not error out, just doesn't do the delete records we were trying to do
(in one case).

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:51 PM
To: mapics-l@xxxxxxxxxxxx
Subject: RE: AS400 Query Question


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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.