Solved

SQL select fields from different files

Posted on 2001-07-11
13
256 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
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Article by: Justin
In light of the WannaCry ransomware attack that affected millions of Windows machines, you might wonder if your Mac needs protecting. Yes, it does and here is how to do it.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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