Solved

AS400 Query Question

Posted on 2010-08-26
8
803 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
Comment Utility
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:Kdo
Kdo earned 100 total points
Comment Utility
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
Comment Utility
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
 

Author Comment

by:lynn_harris
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 27

Accepted Solution

by:
tliotta earned 250 total points
Comment Utility
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
Comment Utility
Thanks ... I will check on the QM.  Good to know it will work.
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
Comment Utility
Awesome.  Thank you very much.  This will help a ton.   :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Occasionally Windows/Microsoft Updates will fail to update. We have found a code that will delete all temporary files and re-register all dll's related to Windows/Microsoft Updates! This works 99% of the time to get the updates working again! The…
Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now