Probably fairly basic PL/SQL question from a PL newbie.
Probably fairly basic PL/SQL question from a PL newbie.
(OP)
Being new to PL/SQL, I am having some difficulty in determining how to use PL/SQL to automate a data-cleaning problem. I have written the appropriate SQL to extract recordsets for 21 years of data that contains the year, harvester ID, harvester name, delivery location, total quantity, and total sale price of harvested goods. The data are sorted such that each harvester's data is grouped by year and the records arranged in order of most frequent to least frequent delivery location for the year. My task is to reduce the multiple records within one year to one principal delivery location using the following rules:
If one location has more deliveries than any other that is the principal location. If multiple locations are tied, the one at which the summed sale price which is greatest identifies the principal location.
Can suggest a PL/SQL procedure that I can use to automate the selection of the principal delivery location for each harvester? The alternative is spending 3-5 days per year of data making the selection manually in an Excel spreadsheet.
Thanks in advance for any help you can offer.
If one location has more deliveries than any other that is the principal location. If multiple locations are tied, the one at which the summed sale price which is greatest identifies the principal location.
Can suggest a PL/SQL procedure that I can use to automate the selection of the principal delivery location for each harvester? The alternative is spending 3-5 days per year of data making the selection manually in an Excel spreadsheet.
Thanks in advance for any help you can offer.
Talk To Other Members
RE: Probably fairly basic PL/SQL question from a PL newbie.
Welcome to our Oracle fora.
To help us help you better, could you please post what you have so far, albeit incomplete? Then point out what your greatest "pain point" is.
Thanks,
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."
RE: Probably fairly basic PL/SQL question from a PL newbie.
I have created a temporary table using the following SQL:
CREATE TABLE prinport_90 AS SELECT DISTINCT vf.year, vf.agid, vf.drvid, cg.vname, apr.description AS port_name,
COUNT(apr.description) as landings, ROUND(SUM(lwt_lbs)) AS lbs, ROUND(SUM(rev)) AS dols
FROM apr, vdrfd vf
INNER JOIN pc ON pc.pcid = vf.pcid
LEFT OUTER JOIN cg on vf.drvid = cg.vid AND cg.latest = 'T'
WHERE vf.year = 1990
AND vf.agid IN ('C','O','W')
AND vf.pargrp IN ('C','I')
AND apr.agid = vf.agid
AND apr.port = vf.port
AND SUBSTR(vf.drvid, 1, 3) <>'ZZZ'
GROUP BY vf.year, vf.agid, vf.drvid, cg.vname, apr.description
ORDER BY vf.agid asc, vf.drvid asc, landings desc, dols desc;
which contains the year, state_code, unique harvester ID, harvester name, delivery location, count of deliveries, and sum of weights and revenues from the deliveries to that location. The sort order of the table ensures that the first entry for each harvester will be one which has the greatest number of deliveries for that harvester in that year. It could be followed by another location that had the same number of deliveries, but by sorting last on the revenue "dols" to location if there is such a tie the location with the greater revenue paid will be listed first. Therefore, all I need is a PL script that selects and outputs the first record for each harvester.
I don't have the PL started because I haven't been able to find any examples of PL mixed that are similar enough to allow me to see how I would write a procedure that would move correctly from the first record of one group in the table (or query) to the first record of the next group, which is exactly what I need. Stated another way, I don't know from the references I have (Feuerstein's Oracle PL/SQL Programming by O'Reilly, and Oracle PL/SQL for Dummies by Wiley)
RE: Probably fairly basic PL/SQL question from a PL newbie.
I'm not sure why your first solution-environment choice was PL/SQL. I believe that the best solution environment for your needs is "plain" SQL with the addition of Oracle extremely powerful Analytic Function, "RANK()".
Thank you for posting the code to "CREATE TABLE PRINPORT_90...". But since we didn't get the code to create the tables from which you are creating PRINPORT_90, I won't be able to tailor a solution to your exact needs. Instead, I'll post code that should be similar to what you can use to solve your need.
First, here is a straight SELECT of the 25 rows in an employee table, EMP:
CODE
select dept_id, id, last_name, salary from emp; DEPT_ID ID LAST_NAME SALARY -------- --- ------------------- ------- 50 1 Velasquez 2,500 41 2 Ngao 1,450 31 3 Nagayama 1,400 10 4 Quick-To-See 1,450 50 5 Ropeburn 1,550 41 6 Urguhart 1,200 42 7 Menchu 1,250 43 8 Biri 1,100 44 9 Catchpole 1,300 45 10 Havel 1,307 31 11 Magee 1,400 32 12 Giljum 1,490 33 13 Sedeghi 1,515 34 14 Nguyen 1,525 35 15 Dumas 1,450 41 16 Maduro 1,400 41 17 Smith 940 42 18 Nozaki 1,200 42 19 Patel 795 43 20 Newman 750 43 21 Markarian 850 44 22 Chang 800 34 23 Patel 795 45 24 Dancs 860 45 25 Schwartz 1,100 25 rows selected.Next is code that displays the same 25 rows, grouped (partitioned) by DEPT_ID and sorted within DEPT_ID by highest to lowest SALARY, and a rank number (RNK) by SALARY. (If two employees have the same salary within the same department, then they have the same rank.):
CODE
select dept_id ,rank() over (partition by dept_id order by salary desc) RNK ,id ,last_name ,salary from emp / DEPT_ID RNK ID LAST_NAME SALARY -------- ---------- --- ------------ ------- 10 1 4 Quick-To-See 1,450 31 1 3 Nagayama 1,400 31 1 11 Magee 1,400 32 1 12 Giljum 1,490 33 1 13 Sedeghi 1,515 34 1 14 Nguyen 1,525 34 2 23 Patel 795 35 1 15 Dumas 1,450 41 1 2 Ngao 1,450 41 2 16 Maduro 1,400 41 3 6 Urguhart 1,200 41 4 17 Smith 940 42 1 7 Menchu 1,250 42 2 18 Nozaki 1,200 42 3 19 Patel 795 43 1 8 Biri 1,100 43 2 21 Markarian 850 43 3 20 Newman 750 44 1 9 Catchpole 1,300 44 2 22 Chang 800 45 1 10 Havel 1,307 45 2 25 Schwartz 1,100 45 3 24 Dancs 860 50 1 1 Velasquez 2,500 50 2 5 Ropeburn 1,550 25 rows selected.Notice that DEPT_ID 41 has four employees with four different salaries, therefore, there are four ranks: 1, 2, 3, and 4.
Now, to display just the top rankings for each group, you can place the code, above, into an "in-line" view, and use "RNK" in the outer WHERE clause to obtain just the top-ranked rows for each group:
CODE
select * from (select dept_id ,rank() over (partition by dept_id order by salary desc) RNK ,id ,last_name ,salary from emp) where RNK = 1 / DEPT_ID RNK ID LAST_NAME SALARY -------- ---------- --- ------------ ------- 10 1 4 Quick-To-See 1,450 31 1 3 Nagayama 1,400 31 1 11 Magee 1,400 32 1 12 Giljum 1,490 33 1 13 Sedeghi 1,515 34 1 14 Nguyen 1,525 35 1 15 Dumas 1,450 41 1 2 Ngao 1,450 42 1 7 Menchu 1,250 43 1 8 Biri 1,100 44 1 9 Catchpole 1,300 45 1 10 Havel 1,307 50 1 1 Velasquez 2,500 13 rows selected.The above model should allow you to apply the technique to your PRINPORT_90 table to obtain similar results.
If you have trouble making the jump from the example to your PRINPORT_90 table, please post your questions here.
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."