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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ... I will check on the QM. Good to know it will work.
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
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
ASKER
Awesome. Thank you very much. This will help a ton. :)
ASKER
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.