cancel
Showing results for 
Search instead for 
Did you mean: 

Any PL/SQL Wizards out there?

Lurker
Grafter
Posts: 1,867
Registered: ‎23-10-2008

Any PL/SQL Wizards out there?

I have a huge table, which contains a record for every transaction which has an effect on our inventory (yup - BIG table!)
For a given transaction type 'CHC' (CHange Costs) I want to return the Part code, Transaction Date and Transaction cost of the LAST TWO changes.
Because its to be used for tracking updates to the cost of materials, and further for calculating the ongoing effect of these, I just need the two for now.
So,
Table is I50F
Pertinent columns are;
I50PART - Part Code
I50TDAT - Transaction Date
I50UCOST - Price changed to
I50TRNS - Transaction Type - we just want CHC

Sample Data (Including just columns we are interested in)
I50PART              I50TDAT             I50UCOST         I50TRNS 

BACCA001            08/03/2006 07:34:51 0.08829          CHC   
BACCA001            25/07/2007 08:26:30 0.10329          CHC   
BACCA001            10/04/2008 16:29:02 0.10639          CHC   
BACCA003            20/06/2006 12:22:30 0.16814          CHC   
BACCA003            25/07/2007 08:26:54 0.17024          CHC   
BACCA003            10/04/2008 13:30:12 0.17535          CHC   
BACCA004            28/08/2007 15:46:03 0.06486          CHC   
BACCA004            28/08/2007 15:49:15 0.06328          CHC   
BACCA004            30/10/2008 09:22:40 0.06952          CHC   
BACCA004            13/01/2009 09:09:07 0.06867          CHC   
BACCA005            25/07/2007 08:27:24 0.06715          CHC   
BACCA005            10/04/2008 15:45:14 0.06916          CHC   
BACCA005            30/10/2008 09:05:17 0.07453          CHC   
BACCA005            13/01/2009 09:06:49 0.07275          CHC


To take a part in isolation, BACCA005;
I'm interested in the last two records.
It makes sense for there to be two records output per part at this stage, as it may be that the powers that be decide that they want the last 3, or 4, or whatever (I'm sure everybody has similar experiences with beancouters)

Is it A) Easy, and B) relatively efficient. There are 2.4m records in the table.
6 REPLIES
Moderator
Moderator
Posts: 19,286
Thanks: 2,165
Fixes: 348
Registered: ‎11-01-2008

Re: Any PL/SQL Wizards out there?

hmmm..
a rough first stab


    CURSOR C1 IS
SELECT DISTINCT I50PART FROM  150F;
BEGIN
FOR PARTREC IN C1
SELECT
I50PART,I50TDAT,
I50UCOST,I50TRNS
FROM
   (SELECT I50PART,I50TDAT,
I50UCOST,I50TRNS
      FROM I50F
    ORDER BY I50TDAT DESC)
    WHERE
   I50PART = C1.150PART
   and I50TRNS = 'CHC'
   and ROWNUM < 2
END LOOP;
END


Customer / Moderator / If it helped click the thumb / If it fixed it click 'This fixed my problem'

Lurker
Grafter
Posts: 1,867
Registered: ‎23-10-2008

Re: Any PL/SQL Wizards out there?

SELECT I50PART, I50TDAT, I50UCOST, I50TRNS
FROM  (SELECT I50PART, I50TDAT, I50UCOST, I50TRNS, row_number() over (PARTITION BY I50PART ORDER BY I50TDAT DESC) rn
        FROM  I50F
        WHERE  I50TRNS = 'CHC')
WHERE  rn <= 2
ORDER BY I50PART, I50TDAT DESC;

This is what was suggested to me. - Looks pretty similar!
Community Veteran
Posts: 3,817
Thanks: 449
Fixes: 6
Registered: ‎05-04-2007

Re: Any PL/SQL Wizards out there?

I'm a SQL Server person, and as a (very) rough stab, without actually trying this out, I would say:
SELECT TOP 2 I50PART, I50TDAT, I50UCOST FROM I50F
WHERE I50TRNS = 'CHC'
ORDER BY I50TDAT DESC

EDIT: WHERE has to come before ORDER BY Smiley
Moderator
Moderator
Posts: 19,286
Thanks: 2,165
Fixes: 348
Registered: ‎11-01-2008

Re: Any PL/SQL Wizards out there?

If you use Order By in oracle in renders the top N bit useless as it will simply extract the top 10 records in the table then order it instead of ordering them then select the top 10.

Customer / Moderator / If it helped click the thumb / If it fixed it click 'This fixed my problem'

Community Veteran
Posts: 3,817
Thanks: 449
Fixes: 6
Registered: ‎05-04-2007

Re: Any PL/SQL Wizards out there?

Ah - I see .. told you I'm very used to SQL Server Smiley
Sure that works in SQL Sever .. hang on .. yep it does. I have a similar table here and I've just tried a:
SELECT     TOP 2 *
FROM        tblMessage
ORDER BY DateCreated DESC

Which seems to work. I guess for others which TOP before the ORDER BY then you'd need to TOP from a dervied table, which the previous queries are doing.
Moderator
Moderator
Posts: 19,286
Thanks: 2,165
Fixes: 348
Registered: ‎11-01-2008

Re: Any PL/SQL Wizards out there?

yeah it's one of the few things I prefer about SQL server..
@james in terms of efficiency I'd guess an explain plan on each one is probably best to answer that question. Though mine night need some tweaking Wink

Customer / Moderator / If it helped click the thumb / If it fixed it click 'This fixed my problem'