jjjjjjj
asked on
SQL select fields from different files
I have two files:
ITH
FRT
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.
jjjjjjj
jjjjjjj
ITH
FRT
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.
jjjjjjj
jjjjjjj
ASKER
I agree with the JOIN feature you mention. But how can I get the field value to sum? Is there anyway this is possible in the cursor, or do I need to do something different?
jjjjjjj
jjjjjjj
If you need sum, you can create an extra column in the table and fill it with the sum. After that you can use this field in your final SQL.
Hope that this helps.
Hope that this helps.
ASKER
What is the code to add the extra column with the sum?
Please refer to my code above and edit as needed.
jjjjjjj
Please refer to my code above and edit as needed.
jjjjjjj
First of all you have to figure out what sum do you want. In accordance with your SQL, even if you manage to get sum(TQTY) with other fields, you don't want to have the same value in every row. Or if you do want it, then calculate the sum of the TQTY and put it into variable.
ASKER
This question has been posted for almost a week now and I still do not have the original requested data or code. Please provide specific code to support what I am trying to do. Without the code, I will remove the question.
jjjjjjj
jjjjjjj
What are the Linking Fields between ITH & FRT. However here is an example assuming a basic structure of Account Header with Account Posting : -
ACCTHDR has ACID, ACDESC, ACPRSD, ACPRED
ACCPOST has PSID, PSDATE, PSVAL
Declare C1 Cursor For
Select ACID, ACDESC, ACPRD, SUM(PSVAL) from ACCTHDR, ACCPOST where ACID = PSID and PSDATE >= ACPRSD and PSDATE <= ACPRED Group By ACID, ACDESC, ACPRD
ACCTHDR has ACID, ACDESC, ACPRSD, ACPRED
ACCPOST has PSID, PSDATE, PSVAL
Declare C1 Cursor For
Select ACID, ACDESC, ACPRD, SUM(PSVAL) from ACCTHDR, ACCPOST where ACID = PSID and PSDATE >= ACPRSD and PSDATE <= ACPRED Group By ACID, ACDESC, ACPRD
ASKER
I tried this structure of code, but got an error when I attempted to validate using STRSQL at the command line.
Is it important where the sum(field) is placed? I notice that you have it as the last field. Is this by design or random?
jjjjjjj
Is it important where the sum(field) is placed? I notice that you have it as the last field. Is this by design or random?
jjjjjjj
Hmmm This should have worked. I have just tried the structure with our database. The sum doesnt have to be last. What Error did you get, and can you post the SQL code? oh by the way you didnt validate the Declare Cursor bit as well did you cos you cant in STRSQL.
Just reviewed my code and Ive noticed the Error. SQL statement should be for the above tables as follows.
Select ACID, ACDESC, ACPRSD, SUM(PSVAL) from ACCTHDR, ACCPOST where ACID = PSID and PSDATE >= ACPRSD
and PSDATE <= ACPRED Group By ACID, ACDESC, ACPRSD
Sorry should have checked my field spellings!!
Select ACID, ACDESC, ACPRSD, SUM(PSVAL) from ACCTHDR, ACCPOST where ACID = PSID and PSDATE >= ACPRSD
and PSDATE <= ACPRED Group By ACID, ACDESC, ACPRSD
Sorry should have checked my field spellings!!
ASKER
I believe that I have figured out the problem:
1. I used inner join to link the files and it worked. I also removed the inner join and used the WHERE statement to join by fields. I am noticing that the WHERE x=y and a=b and c=d statments actually creates a multi-field keylist. Please advise if you agree. Also, please advise if you think this is a good technique or if the inner/left/right join should be used instead.
2. I grouped by all of the fields selected from the files. P_S_Price, I noticed you did this in your SQL.
Is it mandatory to group by all of the selected fields in the SQL. If so, then why? Let me know your thoughts, and then I will forward the points on to you.
Thanks for the help.
jjjjjjj
1. I used inner join to link the files and it worked. I also removed the inner join and used the WHERE statement to join by fields. I am noticing that the WHERE x=y and a=b and c=d statments actually creates a multi-field keylist. Please advise if you agree. Also, please advise if you think this is a good technique or if the inner/left/right join should be used instead.
2. I grouped by all of the fields selected from the files. P_S_Price, I noticed you did this in your SQL.
Is it mandatory to group by all of the selected fields in the SQL. If so, then why? Let me know your thoughts, and then I will forward the points on to you.
Thanks for the help.
jjjjjjj
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 for the help.
jjjjjjj
jjjjjjj
In general if you want to have fields from several tables then you can use join. For example
sSQL = "select * from cwcpf/iim LEFT JOIN v64bpcscpf/ium on" & _
" iprod = umprod And iums = umfum" & _
" order by iclas, iprod"
(as I see you work with BPCS and must be familiar with the file that I mentioned)
Joins demonstrate the real power of a relational database. By joining two tables, you can create a flat
(two-dimensional) view of a one-to-many or many-to-many relationship.