Home » SQL & PL/SQL » SQL & PL/SQL » Getting duplicate records (Oracle 10g)
Getting duplicate records [message #424696] Mon, 05 October 2009 07:04 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to find the top 5 records for each lot_no. The insert data I have given is less to get 5.
But I have to overcome the duplicate values as you can see that I am getting the same LOT_NO in the same ingredient_code.
The lot_no shouldn't repeat. I should get 5 LOT_NO per ingredient_code.
If there are more than one lot_no on different dates under date_received column, I should get the older date value.
So in this case I should get only the lot_no 'N0969787' with date_received '7/1/2009 6:13:40 PM' and
another lot_no 'N0970158' with date_receievd '7/13/2009 7:08:33 PM'.
I have to show the records in order of dateno....so I am not able to find a way as how to display the records.
Please tell me a way to get rid of the duplicate lot_no under one ingredient_code.

Please find the table create script and some inserts.
CREATE TABLE VIEW_PROCESS
(
  INGREDIENT_ID            VARCHAR2(20),
  INGREDIENT_CODE          VARCHAR2(6),
  LOT_NO                   VARCHAR2(10),
  AVAILABLE_QTY            NUMBER(6),
  FORMULA_ID               VARCHAR2(20),
  MINERAL_ID               VARCHAR2(20),
  MINERAL_CODE             VARCHAR2(20),
  DATE_RECEIVED            DATE,
  LOT_PROCESSING_REQUIRED  VARCHAR2(20)
)


INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'INGREDIENT_ID', 'INGRED', 'LOT_NO', 0, 'FORMULA_ID', 'MINERAL_ID', 'MINERAL_CODE'
, NULL, 'LOT_PROCESSING_REQUI'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0969787', 18550, '213', '102', 'Protein',  TO_Date( '07/01/2009 06:13:40 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0969787', 18550, '213', '102', 'Protein',  TO_Date( '07/16/2009 06:49:36 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '105', 'Ca',  TO_Date( '07/13/2009 07:08:33 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '108', 'Na',  TO_Date( '07/13/2009 07:08:33 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '102', 'Protein',  TO_Date( '07/13/2009 07:08:33 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '109', 'K',  TO_Date( '07/13/2009 07:08:33 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '106', 'P',  TO_Date( '07/13/2009 07:08:33 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '107', 'Mg',  TO_Date( '07/13/2009 07:08:33 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '110', 'Cl',  TO_Date( '07/13/2009 07:08:33 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '106', 'P',  TO_Date( '07/19/2009 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '108', 'Na',  TO_Date( '07/19/2009 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '107', 'Mg',  TO_Date( '07/19/2009 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '109', 'K',  TO_Date( '07/19/2009 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '110', 'Cl',  TO_Date( '07/19/2009 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '102', 'Protein',  TO_Date( '07/19/2009 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'28', 'X9147', 'N0970158', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
COMMIT;



Regards,
Mahi
Re: Getting duplicate records [message #424701 is a reply to message #424696] Mon, 05 October 2009 07:25 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thanks for the table and inserts script, but I'm still not clear on what you want the output to look like.
Care to give us an example?
Re: Getting duplicate records [message #424705 is a reply to message #424696] Mon, 05 October 2009 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also if (in addition to the result) you could post a simpler example with only the relevant columns, it should be great.
In your explaination you only mention 4 columns: lot_no, ingredient_code, date_received and dateno (which is not part of the table), post an example with the result with only these 3 columns.

Regards
Michel
Re: Getting duplicate records [message #424763 is a reply to message #424705] Mon, 05 October 2009 23:00 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Michel,
I am so sorry that I forgot to pate the query I wrote.

SELECT   ingredient_code, lot_no, available_qty, lot_processing_required,
         dateno, date_received
    FROM (SELECT   ingredient_code, lot_no, available_qty, date_received,
                   lot_processing_required,
                   ROW_NUMBER () OVER (PARTITION BY ingredient_code ORDER BY date_received ASC NULLS LAST)
                                                                       dateno
              FROM (SELECT DISTINCT ingredient_code, lot_no, available_qty,
                                    date_received, lot_processing_required
                               FROM view_process
                              WHERE formula_id = 213 AND available_qty > 0)
          ORDER BY                                            --DATE_RECEIVED,
                   dateno)
   WHERE dateno <= 5
--AND DATE_RECEIVED IS NOT NULL AND AVAILABLE_QTY >0
ORDER BY ingredient_code, lot_no;


Dateno is an alias given to a subquery which helps in getting know the rank of the record. This is not required in final selection...but selected here only to test.

Regards,
Mahi
Re: Getting duplicate records [message #424765 is a reply to message #424763] Mon, 05 October 2009 23:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't ask for your query but among other things for the result you want with the data you gave.

Regards
Michel
Re: Getting duplicate records [message #424769 is a reply to message #424765] Mon, 05 October 2009 23:28 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I am getting 4 records where there are two duplicate record records with respect to lot_no. I should not get records with repeating lot_no. If there are two lot_no with two dates then I should get the one which has older date. This is where I am not getting the correct result.

From the given query I am getting 4 records but I should get only two records.....two lot_no with older dates.
Re: Getting duplicate records [message #424770 is a reply to message #424769] Mon, 05 October 2009 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 25044
Registered: January 2009
Location: SoCal
Senior Member
monasingh wrote on Mon, 05 October 2009 21:28
I am getting 4 records where there are two duplicate record records with respect to lot_no. I should not get records with repeating lot_no. If there are two lot_no with two dates then I should get the one which has older date. This is where I am not getting the correct result.

From the given query I am getting 4 records but I should get only two records.....two lot_no with older dates.

Posting Guidelines state to SHOW not describe or explain.
Re: Getting duplicate records [message #424772 is a reply to message #424769] Mon, 05 October 2009 23:38 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
From the given query I am getting 4 records but I should get only two records.....two lot_no with older dates.


If I include lot_no as a part of Partition by clause I get two rows.
SELECT   ingredient_code, lot_no, available_qty, lot_processing_required,
         dateno, date_received
    FROM (SELECT   ingredient_code, lot_no, available_qty, date_received,
                   lot_processing_required,
                   ROW_NUMBER () OVER (PARTITION BY ingredient_code,lot_no ORDER BY date_received ASC NULLS LAST)
                                                                       dateno
              FROM (SELECT DISTINCT ingredient_code, lot_no, available_qty,
                                    date_received, lot_processing_required
                               FROM view_process
                              WHERE formula_id = 213 AND available_qty > 0)
          ORDER BY                                            --DATE_RECEIVED,
                   dateno)
   WHERE dateno = 1
--AND DATE_RECEIVED IS NOT NULL AND AVAILABLE_QTY >0
ORDER BY ingredient_code, lot_no;




INGRED LOT_NO     DATE_RECEIVED            DATENO
------ ---------- -------------------- ----------
X9147  N0969787   01-Jul-2009 18:13:40          1
X9147  N0970158   13-Jul-2009 19:08:33          1

2 rows selected.

[Updated on: Mon, 05 October 2009 23:39]

Report message to a moderator

Re: Getting duplicate records [message #424773 is a reply to message #424770] Mon, 05 October 2009 23:40 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I have given the create table with insert script and sql query. If they are run, then you can get a clear idea of the record I am getting. Please run them if possible. Main problem I am getting to get the distinct lot_no is because of the column date_received. I do not need that column in final select. I have put her only for test. I only need the columns (ingredient_code, lot_no, available_qty, lot_processing_required) in final select.

I have to get the records in order of ingredient_code, lot_no
where I have to get the first five records from each lot_no in order of date_received ASC. I have tried to that but only when there are more than one record for lot_no with different date, I am getting the problem.


Re: Getting duplicate records [message #424779 is a reply to message #424773] Tue, 06 October 2009 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this so hard to show us and post what you want?
Please do it.
For myself, I don't try to find a solution to a post that does not contain this information.

Regards
Michel
Re: Getting duplicate records [message #424820 is a reply to message #424772] Tue, 06 October 2009 02:27 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Bonker,
You changed the query from "dateno <= 5" to "dateno = 1"

It needs to be <=5 only. I have to show the 5 records. I am getting two records per lot_no but since they are duplicates I should get one record per lot_no in this case.

Hi Michel,
Below is the output I need.

INGRED   LOT_NO   QTY   LOT_PROCESS  DATENO    DATE_RECE
------   ------  -----  -----------  --------  --------
X9147    N0969787 18550     1          1       01-JUL-09
X9147    N0970158 14425     1          1       13-JUL-09


If there are more records I can get upto 5 records per ingredient if they all have different lot_no.

I am getting these duplicates for records where there are more lot_no on more than one day. Since I have to get it order by date_received, so I used the date_receievd column in inner query and so I am getting that data too.



[Updated on: Tue, 06 October 2009 02:30]

Report message to a moderator

Re: Getting duplicate records [message #424823 is a reply to message #424820] Tue, 06 October 2009 02:39 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
You changed the query from "dateno <= 5" to "dateno = 1" It needs to be <=5 only. I have to show the 5 records. I am getting two records per lot_no but since they are duplicates I should get one record per lot_no in this case.


Did you run my query see the results returned? It does return the output you expected based on test cases. Now to restrict only to 5 rows if there are more records you just need to tweak the query further to restrict them.

[Update] I just added few more insert cases to your test case to get record count more thant 5 like this

XE@SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABL
E_QTY, FORMULA_ID,
  2  MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES (

  3  '28', 'X9147', '&1', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009 06:34
:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
  4* , '1')
XE@SQL> /
Enter value for 1: N100
old   3: '28', 'X9147', '&1', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009 0
6:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
new   3: '28', 'X9147', 'N100', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009
 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')

1 row created.

XE@SQL> /
Enter value for 1: N200
old   3: '28', 'X9147', '&1', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009 0
6:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
new   3: '28', 'X9147', 'N200', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009
 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')

1 row created.

XE@SQL> /
Enter value for 1: N300
old   3: '28', 'X9147', '&1', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009 0
6:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
new   3: '28', 'X9147', 'N300', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009
 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')

1 row created.

XE@SQL> /
Enter value for 1: N400
old   3: '28', 'X9147', '&1', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009 0
6:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
new   3: '28', 'X9147', 'N400', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009
 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')

1 row created.

XE@SQL> /
Enter value for 1: N500
old   3: '28', 'X9147', '&1', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009 0
6:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
new   3: '28', 'X9147', 'N500', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009
 06:34:48 PM', 'MM/DD/YYYY HH:MI:SS AM')

1 row created.

XE@SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABL
E_QTY, FORMULA_ID,
  2  MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES (

  3  '28', 'X9147', '&1', 14425, '213', '105', 'Ca',  TO_Date( '07/19/2009 06:34
:48 PM', 'MM/DD/YYYY HH:MI:SS AM')
  4* , '1')
XE@SQL> commit;

Commit complete.



then used my changed query, if ofcourse returned more than 5 rows

XE@SQL> ed
Wrote file afiedt.buf

  1   SELECT   ingredient_code, lot_no, available_qty, lot_processing_required,
  2            dateno, date_received
  3       FROM (SELECT   ingredient_code, lot_no, available_qty, date_received,
  4                      lot_processing_required,
  5                    ROW_NUMBER () OVER (PARTITION BY ingredient_code,lot_no
  6                                      ORDER BY date_received ASC NULLS LAST)
  7                                                                      dateno
  8                 FROM (SELECT DISTINCT ingredient_code, lot_no, available_qty
,
  9                                       date_received, lot_processing_required

 10                                  FROM view_process
 11                                 WHERE formula_id = 213 AND available_qty > 0
)
 12             ORDER BY --DATE_RECEIVED,
 13                      dateno)
 14      WHERE dateno = 1
 15   --AND DATE_RECEIVED IS NOT NULL AND AVAILABLE_QTY >0
 16*  ORDER BY ingredient_code, lot_no
 17  /

INGRED LOT_NO     AVAILABLE_QTY LOT_PROCESSING_REQUI     DATENO
------ ---------- ------------- -------------------- ----------
DATE_RECEIVED
--------------------
X9147  N0969787           18550 1                             1
01-Jul-2009 18:13:40

X9147  N0970158           14425 1                             1
13-Jul-2009 19:08:33

X9147  N100               14425 1                             1
19-Jul-2009 18:34:48


INGRED LOT_NO     AVAILABLE_QTY LOT_PROCESSING_REQUI     DATENO
------ ---------- ------------- -------------------- ----------
DATE_RECEIVED
--------------------
X9147  N200               14425 1                             1
19-Jul-2009 18:34:48

X9147  N300               14425 1                             1
19-Jul-2009 18:34:48

X9147  N400               14425 1                             1
19-Jul-2009 18:34:48


INGRED LOT_NO     AVAILABLE_QTY LOT_PROCESSING_REQUI     DATENO
------ ---------- ------------- -------------------- ----------
DATE_RECEIVED
--------------------
X9147  N500               14425 1                             1
19-Jul-2009 18:34:48


7 rows selected.

XE@SQL>



Now I just tweaked the query little more to restrict 5 rows and here are the results

XE@SQL> /

INGRED LOT_NO     AVAILABLE_QTY LOT_PROCESSING_REQUI     DATENO
------ ---------- ------------- -------------------- ----------
DATE_RECEIVED
--------------------
X9147  N0969787           18550 1                             1
01-Jul-2009 18:13:40

X9147  N0970158           14425 1                             1
13-Jul-2009 19:08:33

X9147  N100               14425 1                             1
19-Jul-2009 18:34:48


INGRED LOT_NO     AVAILABLE_QTY LOT_PROCESSING_REQUI     DATENO
------ ---------- ------------- -------------------- ----------
DATE_RECEIVED
--------------------
X9147  N200               14425 1                             1
19-Jul-2009 18:34:48

X9147  N300               14425 1                             1
19-Jul-2009 18:34:48


5 rows selected.

XE@SQL>


I leave it to you work it out the tweaking that is needed because as per forum guidelines I should not spoon feed the answers

[Updated on: Tue, 06 October 2009 03:14]

Report message to a moderator

Re: Getting duplicate records [message #424842 is a reply to message #424823] Tue, 06 October 2009 03:53 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Bonker,
Its not that I need only 5 records but its that I need max 5 record per ingredient_id. We can have 1 to 5 lot_no for each ingredient_id.

Please insert some more records and I hope it will make the thing clear.

INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0864627', 0, '213', '102', 'Protein',  TO_Date( '12/12/2008 09:08:22 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0969582', 10925, '213', '102', 'Protein',  TO_Date( '06/23/2009 06:53:33 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970392', 6950, '213', '102', 'Protein',  TO_Date( '07/21/2009 08:11:30 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970413', 6000, '213', '102', 'Protein',  TO_Date( '07/23/2009 08:30:36 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970414', 17975, '213', '102', 'Protein',  TO_Date( '07/23/2009 08:30:36 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970423', 17775, '213', '102', 'Protein',  TO_Date( '07/23/2009 08:30:36 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970424', 9975, '213', '102', 'Protein',  TO_Date( '07/23/2009 08:30:37 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970481', 17825, '213', '102', 'Protein',  TO_Date( '07/24/2009 07:30:38 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970523', 15975, '213', '102', 'Protein',  TO_Date( '07/28/2009 08:03:42 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970532', 17925, '213', '102', 'Protein',  TO_Date( '07/28/2009 08:03:43 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970542', 17875, '213', '102', 'Protein',  TO_Date( '07/28/2009 08:03:43 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970566', 17950, '213', '102', 'Protein',  TO_Date( '07/28/2009 08:03:44 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970568', 15850, '213', '102', 'Protein',  TO_Date( '07/28/2009 08:03:44 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970569', 2000, '213', '102', 'Protein',  TO_Date( '07/28/2009 08:03:44 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970578', 17825, '213', '102', 'Protein',  TO_Date( '07/28/2009 06:29:14 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970596', 17900, '213', '102', 'Protein',  TO_Date( '07/29/2009 06:42:01 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970636', 14000, '213', '102', 'Protein',  TO_Date( '07/29/2009 06:42:01 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 
INSERT INTO VIEW_PROCESS ( INGREDIENT_ID, INGREDIENT_CODE, LOT_NO, AVAILABLE_QTY, FORMULA_ID,
MINERAL_ID, MINERAL_CODE, DATE_RECEIVED, LOT_PROCESSING_REQUIRED ) VALUES ( 
'27', 'X0194', 'N0970644', 17950, '213', '102', 'Protein',  TO_Date( '07/30/2009 06:52:02 PM', 'MM/DD/YYYY HH:MI:SS AM')
, '1'); 



Now we have two ingredient_id.

If you run the query now, the result would come as :-

INGRED  LOT_NO  AVAI_QTY   LOT_PROCESS  DATENO  DATE_RECE
------  ------- ----------  ----------- ------- ----------
X0194  N0969582   10925        1           1     23-JUN-09
X0194  N0970392   6950         1           2     21-JUL-09
X0194  N0970413   6000         1           3     23-JUL-09
X0194  N0970414   17975        1           4     23-JUL-09
X0194  N0970423   17775        1           5     23-JUL-09
X9147  N0969787   18550        1           3     16-JUL-09
X9147  N0969787   18550        1           1     01-JUL-09
X9147  N0970158   14425        1           2     13-JUL-09
X9147  N0970158   14425        1           4     19-JUL-09


The column dateno and date_received is only for testing. We do not require it for final output.

As you can see that the ingredient 'X0194' has 5 records shown by DATENO as 1 to 5( in order of date_received ASC).

The output is coming correct except that I am getting duplicate records when there are more than one record on different dates for a lot_no as above for lot_no 'N0969787' and 'N0970158'. There are only 2 distinct records for X9147. So it should have shown only two records against the ingredient_id 'X9147'.

The required output is :-

INGRED  LOT_NO  AVAI_QTY   LOT_PROCESS  DATENO  DATE_RECE
------  ------- ----------  ----------- ------- ----------
X0194  N0969582   10925        1           1     23-JUN-09
X0194  N0970392   6950         1           2     21-JUL-09
X0194  N0970413   6000         1           3     23-JUL-09
X0194  N0970414   17975        1           4     23-JUL-09
X0194  N0970423   17775        1           5     23-JUL-09
X9147  N0969787   18550        1           1     01-JUL-09
X9147  N0970158   14425        1           2     13-JUL-09



I should get one record per Lot-NO where date_receievd is old.

DATENO helps to find 5 records in order of date_received.

Let me know if I am not clear.

[Updated on: Tue, 06 October 2009 04:10]

Report message to a moderator

Re: Getting duplicate records [message #424851 is a reply to message #424842] Tue, 06 October 2009 04:09 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
As I told you need to tweak my query further to get the results
XE@SQL> ed
Wrote file afiedt.buf

  1  sELECT   ingredient_code, lot_no, available_qty, lot_processing_required,
  2           dateno, date_received
  3      FROM (SELECT   ingredient_code, lot_no, available_qty, date_received,
  4                     lot_processing_required,
  5                     ROW_NUMBER () OVER (PARTITION BY ingredient_code,lot_no
ORDER BY date_received ASC NULLS LAST)
  6                                                                         date
no
  7                FROM (SELECT DISTINCT ingredient_code, lot_no, available_qty,

  8                                      date_received, lot_processing_required
  9                                 FROM view_process
 10                                WHERE formula_id = 213 AND available_qty > 0)

 11            ORDER BY                                            --DATE_RECEIV
ED,
 12                     dateno)
 13     WHERE dateno = 1
 14  --AND DATE_RECEIVED IS NOT NULL AND AVAILABLE_QTY >0
 15* ORDER BY ingredient_code, lot_no
XE@SQL> /

INGRED LOT_NO     AVAILABLE_QTY LOT_PROCES     DATENO DATE_RECEIVED
------ ---------- ------------- ---------- ---------- --------------------
X0194  N0969582           10925 1                   1 23-Jun-2009 18:53:33
X0194  N0970392            6950 1                   1 21-Jul-2009 08:11:30
X0194  N0970413            6000 1                   1 23-Jul-2009 08:30:36
X0194  N0970414           17975 1                   1 23-Jul-2009 08:30:36
X0194  N0970423           17775 1                   1 23-Jul-2009 08:30:36
X0194  N0970424            9975 1                   1 23-Jul-2009 08:30:37
X0194  N0970481           17825 1                   1 24-Jul-2009 19:30:38
X0194  N0970523           15975 1                   1 28-Jul-2009 08:03:42
X0194  N0970532           17925 1                   1 28-Jul-2009 08:03:43
X0194  N0970542           17875 1                   1 28-Jul-2009 08:03:43
X0194  N0970566           17950 1                   1 28-Jul-2009 08:03:44

INGRED LOT_NO     AVAILABLE_QTY LOT_PROCES     DATENO DATE_RECEIVED
------ ---------- ------------- ---------- ---------- --------------------
X0194  N0970568           15850 1                   1 28-Jul-2009 08:03:44
X0194  N0970569            2000 1                   1 28-Jul-2009 08:03:44
X0194  N0970578           17825 1                   1 28-Jul-2009 18:29:14
X0194  N0970596           17900 1                   1 29-Jul-2009 18:42:01
X0194  N0970636           14000 1                   1 29-Jul-2009 18:42:01
X0194  N0970644           17950 1                   1 30-Jul-2009 18:52:02
X9147  N0969787           18550 1                   1 01-Jul-2009 18:13:40
X9147  N0970158           14425 1                   1 13-Jul-2009 19:08:33

19 rows selected.

XE@SQL> /* Now I kept dateno = 1 as it is just added further condition to derive this result */

XE@SQL> /

INGRED LOT_NO     AVAILABLE_QTY LOT_PROCES     DATENO DATE_RECEIVED
------ ---------- ------------- ---------- ---------- --------------------
X0194  N0969582           10925 1                   1 23-Jun-2009 18:53:33
X0194  N0970392            6950 1                   1 21-Jul-2009 08:11:30
X0194  N0970413            6000 1                   1 23-Jul-2009 08:30:36
X0194  N0970414           17975 1                   1 23-Jul-2009 08:30:36
X0194  N0970423           17775 1                   1 23-Jul-2009 08:30:36
X9147  N0969787           18550 1                   1 01-Jul-2009 18:13:40
X9147  N0970158           14425 1                   1 13-Jul-2009 19:08:33

7 rows selected.

XE@SQL>



[Update] Hint you need to further inline query along with one more row_number() over analytical function to achieve the results

[Updated on: Tue, 06 October 2009 04:15]

Report message to a moderator

Re: Getting duplicate records [message #424854 is a reply to message #424851] Tue, 06 October 2009 04:28 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

You mean I should take the whole query as a inline query and use in a similar analytical function.... I am trying...
Re: Getting duplicate records [message #424860 is a reply to message #424854] Tue, 06 October 2009 04:54 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
You mean I should take the whole query as a inline query and use in a similar analytical function.... I am trying...



Yes that's how I wrote it.
Re: Getting duplicate records [message #424869 is a reply to message #424851] Tue, 06 October 2009 06:10 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Bonker,
I tried and got the result...but the query looks very big.....

SELECT   ingredient_code, lot_no, available_qty, lot_processing_required
    FROM (SELECT   ingredient_code, lot_no, available_qty,
                   lot_processing_required,                   
                   ROW_NUMBER () OVER (PARTITION BY ingredient_code ORDER BY date_received ASC NULLS LAST)RANK
              FROM (SELECT   ingredient_code, lot_no, available_qty,
                             lot_processing_required, date_received, dateno
                        FROM (SELECT   ingredient_code, lot_no, available_qty,
                                       date_received, lot_processing_required,
                                       ROW_NUMBER () OVER (PARTITION BY ingredient_code, lot_no ORDER BY date_received ASC NULLS LAST) dateno
                                  FROM (SELECT DISTINCT ingredient_code,
                                                        lot_no, available_qty,
                                                        date_received,
                                                        lot_processing_required
                                                   FROM view_process
                                                  WHERE formula_id = 213
                                                    AND available_qty > 0)
                              ORDER BY dateno)
                       WHERE dateno = 1
                    ORDER BY ingredient_code, lot_no)
          ORDER BY RANK)
   WHERE RANK <= 5
ORDER BY ingredient_code, lot_no
Re: Getting duplicate records [message #424881 is a reply to message #424869] Tue, 06 October 2009 06:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Get rid of the ORDER BY clauses in the sub-queries - you're not selecting based on rownum anywhere, so each of them is just doing work that is undone by the next order by.
Re: Getting duplicate records [message #424882 is a reply to message #424881] Tue, 06 October 2009 06:57 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Jrow,
I removed the inner order by and got same result.


Re: Getting duplicate records [message #424883 is a reply to message #424882] Tue, 06 October 2009 07:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that you will be able to replace the innermost two queries
SELECT   ingredient_code, lot_no, available_qty,
                                       date_received, lot_processing_required,
                                       ROW_NUMBER () OVER (PARTITION BY ingredient_code, lot_no ORDER BY date_received ASC NULLS LAST) dateno
                                  FROM (SELECT DISTINCT ingredient_code,
                                                        lot_no, available_qty,
                                                        date_received,
                                                        lot_processing_required
                                                   FROM view_process
                                                  WHERE formula_id = 213
                                                    AND available_qty > 0)
                              ORDER BY dateno

with a single query:
                              SELECT   ingredient_code, lot_no, available_qty,
                                       date_received, lot_processing_required,
                                       ROW_NUMBER () OVER (PARTITION BY ingredient_code, lot_no ORDER BY date_received ASC NULLS LAST) dateno
                                 FROM  view_process
                                WHERE  formula_id = 213
                                  AND  available_qty > 0
Re: Getting duplicate records [message #424886 is a reply to message #424869] Tue, 06 October 2009 07:12 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
Here is another way of doing it. You need to benchmark and test both the queries

XE@SQL> ed
Wrote file afiedt.buf

  1  select
  2     ingredient_code,
  3     lot_no,
  4     available_qty,
  5     lot_processing_required
  6  from
  7  (
  8  SELECT
  9     ingredient_code,
 10     lot_no,
 11     min_date_received,
 12          available_qty,
 13     lot_processing_required ,
 14          row_number() over (partition by ingredient_code order by mi
eceived asc nulls last) rno
 15  from
 16  (
 17  SELECT     distinct
 18     ingredient_code,
 19     lot_no,
 20          min(date_received) over
 21     (partition by ingredient_code,  lot_no) min_date_received,
 22          date_received      ,
 23     available_qty,
 24     lot_processing_required
 25  FROM view_process
 26  WHERE formula_id = '213' AND available_qty > 0
 27  )
 28  where min_date_received = date_received
 29  )
 30  where rno < =5
 31* order by   ingredient_code,        lot_no
XE@SQL> /

INGRED LOT_NO     AVAILABLE_QTY LOT_PROCESSING_REQUI
------ ---------- ------------- --------------------
X0194  N0969582           10925 1
X0194  N0970392            6950 1
X0194  N0970413            6000 1
X0194  N0970414           17975 1
X0194  N0970423           17775 1
X9147  N0969787           18550 1
X9147  N0970158           14425 1

7 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1944468992

------------------------------------------------------------------------

----------

| Id  | Operation                 | Name         | Rows  | Bytes | Cost
Time     |

------------------------------------------------------------------------

----------

|   0 | SELECT STATEMENT          |              |    17 |   850 |     7
00:00:01 |

|   1 |  SORT ORDER BY            |              |    17 |   850 |     7
00:00:01 |

|*  2 |   VIEW                    |              |    17 |   850 |     6
00:00:01 |

|*  3 |    WINDOW SORT PUSHED RANK|              |    17 |   935 |     6
00:00:01 |

|*  4 |     VIEW                  |              |    17 |   935 |     5
00:00:01 |

|   5 |      HASH UNIQUE          |              |    17 |   629 |     5
00:00:01 |

|   6 |       WINDOW SORT         |              |    17 |   629 |     5
00:00:01 |

|*  7 |        TABLE ACCESS FULL  | VIEW_PROCESS |    17 |   629 |     3
00:00:01 |

------------------------------------------------------------------------

----------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RNO"<=5)
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "INGREDIENT_CODE" ORDER B
              "MIN_DATE_RECEIVED")<=5)
   4 - filter("MIN_DATE_RECEIVED"="DATE_RECEIVED")
   7 - filter("FORMULA_ID"='213' AND "AVAILABLE_QTY">0)

XE@SQL>




XE@SQL> ed
Wrote file afiedt.buf

  1  SELECT   ingredient_code, lot_no, available_qty, lot_processing_required
  2      FROM (SELECT   ingredient_code, lot_no, available_qty,
  3                     lot_processing_required,
  4                     ROW_NUMBER () OVER (PARTITION BY ingredient_code ORDER B
Y date_received ASC NULLS LAST)RANK
  5                FROM (SELECT   ingredient_code, lot_no, available_qty,
  6                               lot_processing_required, date_received, dateno

  7                          FROM (SELECT   ingredient_code, lot_no, available_q
ty,
  8                                         date_received, lot_processing_requir
ed,
  9                                         ROW_NUMBER () OVER (PARTITION BY ing
redient_code, lot_no ORDER BY date_received ASC NULLS LAST) dateno
 10                                    FROM (SELECT DISTINCT ingredient_code,
 11                                                          lot_no, available_q
ty,
 12                                                          date_received,
 13                                                          lot_processing_requ
ired
 14                                                     FROM view_process
 15                                                    WHERE formula_id = '213'
 16                                                      AND available_qty > 0)
 17                               )
 18                         WHERE dateno = 1
 19                      )
 20            )
 21     WHERE RANK <= 5
 22* ORDER BY ingredient_code, lot_no
XE@SQL> /

INGRED LOT_NO     AVAILABLE_QTY LOT_PROCESSING_REQUI
------ ---------- ------------- --------------------
X0194  N0969582           10925 1
X0194  N0970392            6950 1
X0194  N0970413            6000 1
X0194  N0970414           17975 1
X0194  N0970423           17775 1
X9147  N0969787           18550 1
X9147  N0970158           14425 1

7 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4150676541

--------------------------------------------------------------------------------

------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

| Time     |

--------------------------------------------------------------------------------

------------

|   0 | SELECT STATEMENT            |              |    17 |   850 |     7  (58)

| 00:00:01 |

|   1 |  SORT ORDER BY              |              |    17 |   850 |     7  (58)

| 00:00:01 |

|*  2 |   VIEW                      |              |    17 |   850 |     6  (50)

| 00:00:01 |

|*  3 |    WINDOW SORT PUSHED RANK  |              |    17 |  1003 |     6  (50)

| 00:00:01 |

|*  4 |     VIEW                    |              |    17 |  1003 |     5  (40)

| 00:00:01 |

|*  5 |      WINDOW SORT PUSHED RANK|              |    17 |   782 |     5  (40)

| 00:00:01 |

|   6 |       VIEW                  |              |    17 |   782 |     4  (25)

| 00:00:01 |

|   7 |        HASH UNIQUE          |              |    17 |   629 |     4  (25)

| 00:00:01 |

|*  8 |         TABLE ACCESS FULL   | VIEW_PROCESS |    17 |   629 |     3   (0)

| 00:00:01 |

--------------------------------------------------------------------------------

------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RANK"<=5)
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "INGREDIENT_CODE" ORDER BY
              "DATE_RECEIVED")<=5)
   4 - filter("DATENO"=1)
   5 - filter(ROW_NUMBER() OVER ( PARTITION BY "INGREDIENT_CODE","LOT_NO" ORDER
BY

              "DATE_RECEIVED")<=1)
   8 - filter("FORMULA_ID"='213' AND "AVAILABLE_QTY">0)

XE@SQL>

Previous Topic: how to merge two lines into one in one field
Next Topic: Creating Indexes in Views
Goto Forum:
  


Current Time: Wed Dec 07 08:40:45 CST 2016

Total time taken to generate the page: 0.11161 seconds