Solved

SQL select fields from different files

Posted on 2001-07-11
13
242 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now