Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

AS400 Query Question

Posted on 2010-08-26
8
Medium Priority
?
827 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 200 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 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 400 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 400 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 1000 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

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
Hello I read in a discussion about a person who configured a very simple mirror RAID with two hard drives; the system and data were on the same partition. He asked how to repair the system as it was not booting up anymore. In his case running …
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.

670 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