  |
|
|
|
||||||||
 |
 |
|
|
 |
||
|
More Conversion of Query/400 to SQL Hey, Ted: We do exactly the same thing here that Rich does to create Query Management queries. I came across one the other day I didn't know how to solve. 
I had created result fields in the Query/400 query and changed the default size. When I converted the Query/400 query to a QM query and ran it, the output file it created was not the same. I discovered that the QM query was not using my result field size overrides. Since I was trying to use the result field to link to another file, I got no data from the secondary file. How can I define the size of a result field in a QM query? --Ken Don't try to define the size of the result field. Instead, create the file in advance. You can use your Query/400 query, or you can write DDS or SQL to build the file. Edit the source member that was created when you ran the Retrieve Query Management Query (RTVQMQRY) command. You can delete all the junk that appears in the first few records, before the SELECT command begins. Then add an INSERT clause to the beginning of the source member. Here's an example of the edited query:
INSERT INTO SOMEFILE
SELECT
ALL T01.ITEM, T02.IMDESC,
(S1*SALES01+S2*SALES02) AS YTD, T01.SALES01,
T01.SALES02, T01.SALES03, T01.SALES04
FROM SALES T01,
IMIN T02,
SALESSLT T03
WHERE T01.ITEM = T02.IMITNO
AND T01.ITEM <> T03.SID
Use the Create Query Management Query (CRTQMQRY) command to build the new QM query, but do not specify OUTPUT(*OUTFILE). You don't need to tell the name of the output file, because you've specified the file name in the INSERT clause. Before you run the query, be sure to clear the output file first, if necessary. For file processing, Query/400 is not robust enough for my taste. Converting Query/400 queries to QM queries is a good move. Query/400 is okay for reporting, especially ad hoc reporting. Query/400 makes a good prototyping tool; that is, you can use it to help determine the output someone desires, then convert the query to SQL. I think that embedding the generated SQL in programs is an even better alternative than conversion to Query Management. Use RTVQMQRY to generate SQL for a Query/400 query, but don't use CRTQMQRY. Instead, place the generated SQL into a program and compile it. If you decide to go that route, follow the SQL command with a test of the SQL state (SQLSTT) variable. If the SQL state is not equal to five zeros after the query executes, you can take appropriate action. Here's an example in RPG.
D QCMDEXC pr extpgm('QCMDEXC')
D Command 80a const
D CommandLen 15p 5 const
C/exec sql
C+
C+ set option commit=*none,closqlcsr=*endmod
C+
C/end-exec
/free
QCMDEXC('clrpfm join03b':80);
/end-free
C/exec sql
C+
C+ INSERT INTO SOMEFILE
C+ SELECT
C+ ALL T01.ITEM, T02.IMDESC,
C+ (S1*SALES01+S2*SALES02) AS YTD, T01.SALES01,
C+ T01.SALES02, T01.SALES03, T01.SALES04
C+ FROM SALES T01,
C+ IMIN T02,
C+ SALESSLT T03
C+ WHERE T01.ITEM = T02.IMITNO
C+ AND T01.ITEM <> T03.SID
C+
C/end-exec
/free
if SQLSTT <> '00000';
// do something
endif;
*inlr = *on;
/end-free
--Ted
|
Editors
Contact the Editors |
| Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved. |
INFORM DECISIONS
