Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Any PL/SQL Wizards out there?
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Other forums
- :
- Tech Help - Software/Hardware etc
- :
- Re: Any PL/SQL Wizards out there?
Any PL/SQL Wizards out there?
05-03-2009 12:06 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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;
Sample Data (Including just columns we are interested in)
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.
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 6
Re: Any PL/SQL Wizards out there?
05-03-2009 1:14 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
hmmm..
a rough first stab
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'
If it helped click the thumb
If it fixed it click 'This fixed my problem'
Re: Any PL/SQL Wizards out there?
05-03-2009 1:22 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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!
Re: Any PL/SQL Wizards out there?
05-03-2009 1:27 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
I'm a SQL Server person, and as a (very) rough stab, without actually trying this out, I would say:
EDIT: WHERE has to come before ORDER BY
SELECT TOP 2 I50PART, I50TDAT, I50UCOST FROM I50F
WHERE I50TRNS = 'CHC'
ORDER BY I50TDAT DESC
EDIT: WHERE has to come before ORDER BY
Re: Any PL/SQL Wizards out there?
05-03-2009 1:42 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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'
If it helped click the thumb
If it fixed it click 'This fixed my problem'
Re: Any PL/SQL Wizards out there?
05-03-2009 1:49 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Ah - I see .. told you I'm very used to SQL Server
Sure that works in SQL Sever .. hang on .. yep it does. I have a similar table here and I've just tried a:
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.
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.
Re: Any PL/SQL Wizards out there?
05-03-2009 1:55 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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
@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
Customer / Moderator
If it helped click the thumb
If it fixed it click 'This fixed my problem'
If it helped click the thumb
If it fixed it click 'This fixed my problem'
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Other forums
- :
- Tech Help - Software/Hardware etc
- :
- Re: Any PL/SQL Wizards out there?