Probably fairly basic PL/SQL question from a PL newbie.

[MartialArtist]

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.

 

[SantaMufasa]

Martial,

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,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[MartialArtist]

Mufasa,

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)

 

[SantaMufasa]

Martial,

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:

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.):

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:

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.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.