• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 842
  • Last Modified:

AS400 Query Question

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
lynn_harris
Asked:
lynn_harris
4 Solutions
 
momi_sabagCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
mustaccioCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
lynn_harrisAuthor Commented:
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
 
tliottaCommented:
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
 
lynn_harrisAuthor Commented:
Thanks ... I will check on the QM.  Good to know it will work.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
lynn_harrisAuthor Commented:
Awesome.  Thank you very much.  This will help a ton.   :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now