SQL select fields from different files
Posted on 2001-07-11
I have two files:
Neither file is normalized. I want to create an SQL cursor that does the following:
DECLARE ST CURSOR FOR SELECT ITH (the fields I want are TPROD, and the SUM(TQTY), FRT (the fields I want are RWRKC, RLAB where tprod = (select iprod from IIM where TPROD = IPROD and IITYP IN('F','M')) and ttdte between :fromdte and :todte and twhs = :whs and rwrkc between (:fwrkctr and :twrkctr) group by RWRKC.
As a note, the host variables are passed from the screen inputs that the user provides. I won't be using a CL for this application.
I don't know if I have to use a join statement on ITH to FRT link (TPROD to RPROD). Please advise.
I then want to create a data structure to move the values of these fields(record) selected, and refresh the data structure via a fetch with each new record to EOF(SQL = 100).
The records fetched should look like this:
TPROD SUM(TQTY) RWRKC RLAB
abc 10 a1 8.0
xyz 5 a1 2.5
abc 10 a2 1.1
xyz 5 a2 1.3
abc 10 a3 .5
In my modeling of the SQL statement, everything works fine until I add the SUM to the TQTY field. Then the SQL statement fails.
I need the sum because I want to select all transactions in the ITH file within a date range by item and then sum the record quantities of all the tranactions, by item number, to create one record from the ITH item.
Once I have the ITH records summarized for the date range, I want to link to the FRT (Routing master)and create a record for each transaction summary by sequence. The goal is to be able to multiply the summed transaction quantity by the labor hours (RLAB) for each item that passed through the given work center and then sum the total units and hours of actual labor absorption. With this tool I can then compare the actual labor absorbed to the standard and get my efficiency by work center.
1. Please show me how to create the cursor to do this.
2. Please show me how to create a data structure that is not external, but internal for the fetch to move the data elements into.
3. If my design is imperfect or if you know of a better way, then please provide the code with explanation of why you would do it that way. I know I could do it in RPG, but I want to learn more about SQL. My preference is to see examples with SQL cursors.