Solved

SQL select fields from different files

Posted on 2001-07-11
13
251 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:jjjjjjj
[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
  • 6
  • 4
  • 3
13 Comments
 
LVL 28

Expert Comment

by:iboutchkine
ID: 6278234
First of all you cannot have Field and sum in the same statement.
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.
0
 
LVL 1

Author Comment

by:jjjjjjj
ID: 6278355
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
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 6280441
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:jjjjjjj
ID: 6280799
What is the code to add the extra column with the sum?
 Please refer to my code above and edit as needed.


jjjjjjj
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 6281725
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.
0
 
LVL 1

Author Comment

by:jjjjjjj
ID: 6286024
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
0
 
LVL 3

Expert Comment

by:P_S_Price
ID: 6294873
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
0
 
LVL 1

Author Comment

by:jjjjjjj
ID: 6295551
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
0
 
LVL 3

Expert Comment

by:P_S_Price
ID: 6297324
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.
0
 
LVL 3

Expert Comment

by:P_S_Price
ID: 6297336
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!!

0
 
LVL 1

Author Comment

by:jjjjjjj
ID: 6300553
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
0
 
LVL 3

Accepted Solution

by:
P_S_Price earned 100 total points
ID: 6301450
Yes it is Mandatory. Certain Functions only work in a Grouped scenarion (MAX, MIN, AVG, SUM etc etc).

I never fuss about the JOIN LEFT, RIGHT, UP, DOWN, IN, OUT, SHAKEITALLABOUT etc etc unless I need to.

You will need to judge for yourself based on your own requirements.
0
 
LVL 1

Author Comment

by:jjjjjjj
ID: 6302305
Thanks for the help.

jjjjjjj
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article was originally published on Monitis Blog, you can check it  here . If you have responsibility for software in production, I bet you’d like to know more about it. I don’t mean that you’d like an extra peek into the bowels of the sour…
This article discusses how to implement server side field validation and display customized error messages to the client.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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