Solved

AS400 Query Question

Posted on 2010-08-26
8
813 Views
Last Modified: 2013-12-06
This is a general question…. I’m posting it because of the tight timeline.  

I have a repair order master -  the only field I need off this file is location.  However, think of it as the header record.  Then I have three detail files labor, parts, and other.  Each contains detail costing information.  A repair order may have entries on each or only one of the detail files.  Each file is keyed by the repair order number and service date.

I want to create a report which combines all work from the 3 detail files groups by vehicle number and service date.  Providing total repair costs by vehicle subtotaled by service date.  

Does this lend itself to query?  I would normally right a sql to populate one combined file with selected information from all the detail files and then feed it to a print program or query format…… however the user wants this in query.  

Any input is appreciated….. Thanks
0
Comment
Question by:lynn_harris
8 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 50 total points
ID: 33536971
well,
i did not completley follow you there, but everything is possible using SQL !
can you please provide the tables sturcture and the desired output?
0
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 100 total points
ID: 33537140
Hi Lynn,

Is it save to assume that the Master will always contain the repair order number even when one or more of the detail files doesn't?  If so, just outer join the other tables to it and add things up.  :)

Kent

--
--  Get all of the details
--

SELECT *
FROM master m
LEFT OUTER JOIN labor l
  ON m.repairorder = l.repairorder
LEFT OUTER JOIN parts p
  ON m.repairorder = p.repairorder
LEFT OUTER JOIN other o
  ON o.repairorder = o.repairorder;

--
--  Add up all of the details
--

SELECT m.repairorder,
  sum (coalesce (l.amount)) laboramount,
  sum (coalesce (p.amount)) partsamount,
  sum (coalesce (o.amount)) otheramount
FROM master m
LEFT OUTER JOIN labor l
  ON m.repairorder = l.repairorder
LEFT OUTER JOIN parts p
  ON m.repairorder = p.repairorder
LEFT OUTER JOIN other o
  ON o.repairorder = o.repairorder;

Open in new window

0
 
LVL 8

Assisted Solution

by:mustaccio
mustaccio earned 100 total points
ID: 33537209
There may be some quirks in iSeries SQL, but generally this should work:

select m.orderno, u.vehicleid, u.workdate, sum(u.cost)
from
  ordermaster m, (
    select orderno, vehicleid, workdate, cost
    from labor
    union
    select orderno, vehicleid, workdate, cost
     from parts
     union
    select orderno, vehicleid, workdate, cost
     from other
  ) u
where m.orderno = u.orderno
groupby m.orderno, u.vehicleid, u.workdate
 
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:lynn_harris
ID: 33537290
Thank you all.  both solution look like they should work.  I would usually use the sub-select method, but I like the outer join.  I will have to try it.  

However, on the as400 there is a query tool available to the users.  I'm trying to get a feel if this is to complex for this tool to handle.  They would like to know how to create it without involving IT.   It is a tool I'm not very familiar with and they want a solution asap.  

Again, thank you all input is appreciated.  
0
 
LVL 27

Accepted Solution

by:
tliotta earned 250 total points
ID: 33537535
The Query/400 (or Query for iSeries or whatever it is nowadays) product can do this.

I'd strongly recommend that you tell your users to dump using Query/400 and move to something from last century like Query Manager (QM) instead; but still, Query/400 can do it.

QM can also run Query/400 queries if users want to keep old query definitions around. QM has two user interfaces -- a "prompted" mode that carries something of the Query/400 look&feel forward, and a "SQL" mode that should be more obvious to developer types. If the STRQM command exists, then the company already has QM.

Tom
0
 

Author Closing Comment

by:lynn_harris
ID: 33537600
Thanks ... I will check on the QM.  Good to know it will work.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 33550139
I agree with Tom that migrating away from Query/400 is generally a good idea.  IMO QM is a little primitive, too, but has the huge advantage of being SQL-based,  Web Query (http://www.redbooks.ibm.com/abstracts/sg247214.html) or the incredibly versatile Crystal Reports (my preferred cross-platform reporting tool) is generally a good idea.  

One problem with this is that if you have a large base of Query/400 queries, it can be a big job to convert them all.

the RTVQMQRY command can be used to  Query/400 queries (*QRYDFN objects) to QM SQL.  This is a great way to reuse existing Query/400 queries, and can also help if you are a Query/400 pro just learning SQL.

Assuming the source file QQMQRYSRC exists, and you want to retrieve SQL for a *QRYDFN called "MYQUERY":

RTVQMQRY QMQRY(MYLIB/MYQYERY)    
         SRCFILE(MYLIB/QQMQRYSRC)
         ALWQRYDFN(*ONLY)        

- Gary Patterson
0
 

Author Comment

by:lynn_harris
ID: 33550167
Awesome.  Thank you very much.  This will help a ton.   :)
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question