This content has been marked as final. Show 10 replies
Maybe something like
insert when rn = 1 then into unique_rows else into duplicate_rows select s.*, row_number() over (partition by ... order by ...) rn from source_table s
Try INSERT ALL
can u please brief it out . . .
You dont give us much to work with ;)
Assume you have several order versions (same order_no) for the same items (changing quantities) and you want to retain the last order
insert when rn = 1 then into unique_rows (<column_list>) values (<column_list>) else into duplicate_rows (<column_list>) values (<column_list>) select <column_list>, row_number() over (partition by <col_1, ... ,col_n> /* order_no,item_no */ order by <col_1, ... ,col_m> /* date_ordered desc */ ) rn /* specify partition by and order by columns to get duplicates numbered */ /* rows numbered as 1 represent "uniqe" rows the rest are duplicate rows */ from source_table s
the code u specified is good.Can u write it in PLSQL.
yes - wrap itt with create or replace procedure abc (p_table in varchar2,p_out number) is
the code u specified is good.Can u write it in PLSQL.Translation: "can you do all my school work for me"
i have executed ur query but it is displaying as error at line 7- missing right parenthesis
when rn = 1 then
row_number() over (partition by (sid,sname) order by sid) rn
from student s
Edited by: 987184 on May 4, 2013 10:02 PM
Sorry, no Database at hand to test
Seems you're using [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i81407]10g/[url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#i81407]11g syntax option on version [url http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions2a.htm#81409]9i
Maybe <tt>row_number() over (partition by sid,sname order by sid) rn</tt>
Thank you, ur next query worked.
Edited by: 987184 on May 4, 2013 11:03 PM