Home » SQL & PL/SQL » SQL & PL/SQL » Sorting in Merge (Oracle 9i)
Sorting in Merge [message #398093] Wed, 15 April 2009 04:08 Go to next message
carthyc
Messages: 20
Registered: April 2005
Location: chennai
Junior Member
Hi Experts,

I would like to change the order of insertion in my merge statement.

My scenario is,

-- a sequence
create sequence test_seq;

-- a table for test
create table merge_test (day_no integer,day_date date )

-- Merge data

MERGE INTO merge_test A USING
(SELECT rownum day_no,
CURR_WEEK_DATE + ((LEVEL-1)*7) day_date
FROM
(SELECT CURR_WEEK_DATE
FROM
(SELECT CURR_WEEK_DATE,
TO_CHAR(CURR_WEEK_DATE,'D') DAY_NO
FROM
(SELECT SYSDATE - 1 + LEVEL CURR_WEEK_DATE,
LEVEL DAY_COUNT
FROM DUAL CONNECT BY LEVEL <= 7
)
/* DATE OF THE DAY */
)
WHERE DAY_NO = '3'
/* DAY */
) CONNECT BY LEVEL <= 13
)b ON (a.day_no =b.day_no)
WHEN MATCHED THEN
UPDATE SET a.day_date = b.day_date WHEN NOT MATCHED THEN
INSERT
(a.day_no, a.day_date
) VALUES
(test_seq.nextval, b.day_date
);

As I have no data in merge_test table, above merge statment is going to insert records.

Output of my merge is:

select * from merge_test;

1 19-MAY-2009
2 09-JUN-2009
3 05-MAY-2009
4 07-JUL-2009
5 23-JUN-2009
6 02-JUN-2009
7 12-MAY-2009
8 16-JUN-2009
9 14-JUL-2009
10 21-APR-2009
11 28-APR-2009
12 30-JUN-2009

But I would like it to be stored in the same order of source table ( b ).

SELECT rownum day_no,
CURR_WEEK_DATE + ((LEVEL-1)*7) day_date
FROM (SELECT CURR_WEEK_DATE
FROM (SELECT CURR_WEEK_DATE,
TO_CHAR(CURR_WEEK_DATE,'D') DAY_NO
FROM (SELECT SYSDATE - 1 + LEVEL CURR_WEEK_DATE,
LEVEL DAY_COUNT
FROM DUAL CONNECT BY LEVEL <= 7) /* DATE OF THE DAY */ )
WHERE DAY_NO = '3' /* DAY */ )
CONNECT BY LEVEL <= 13

1 21-APR-2009
2 28-APR-2009
3 05-MAY-2009
4 12-MAY-2009
5 19-MAY-2009
6 26-MAY-2009
7 02-JUN-2009
8 09-JUN-2009
9 16-JUN-2009
10 23-JUN-2009
11 30-JUN-2009
12 07-JUL-2009
13 14-JUL-2009

I believe it could be possible. Kindly help me to get this.

Awaiting for your suggestions.
Thanks,
Carthyc
Re: Sorting in Merge [message #398098 is a reply to message #398093] Wed, 15 April 2009 04:19 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you want to sort output of the SELECT statement, use ORDER BY clause.
Re: Sorting in Merge [message #398100 is a reply to message #398093] Wed, 15 April 2009 04:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
I would like to change the order of insertion in my merge statement.
Do you really need it? Mostly we don't care about the order of insertion.

It is the logic how you retrieve the data.

By
Vamsi
Re: Sorting in Merge [message #398102 is a reply to message #398100] Wed, 15 April 2009 04:31 Go to previous messageGo to next message
carthyc
Messages: 20
Registered: April 2005
Location: chennai
Junior Member
Yes..Eventhough I could use Orderby in day_date, I would like data to be stored in a logical order.

Thanks,
Carthyc
Re: Sorting in Merge [message #398104 is a reply to message #398093] Wed, 15 April 2009 04:37 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Data is not stored in ANY order in tables.
So what you're trying to achieve is pointless.
Re: Sorting in Merge [message #398108 is a reply to message #398104] Wed, 15 April 2009 04:44 Go to previous messageGo to next message
carthyc
Messages: 20
Registered: April 2005
Location: chennai
Junior Member
I use to access by key column (day_no), not using date column.
So date of day_no 2 must be smaller than day_no 3.
Re: Sorting in Merge [message #398109 is a reply to message #398108] Wed, 15 April 2009 04:50 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use Analytical Functions like rank, row_number or dense_rank.
(Or) use inner select to order by and get the rownum in outer select.

By
Vamsi
Re: Sorting in Merge [message #398112 is a reply to message #398109] Wed, 15 April 2009 04:58 Go to previous messageGo to next message
carthyc
Messages: 20
Registered: April 2005
Location: chennai
Junior Member
I dont have problem in source table (b) subqueries, I am getting problem in on clause.

Merge statement does some sort of sorting operation to process insert and update statements. There I am getting this issue.

Any hints /*+ */ will help to solve this!!!

[Updated on: Wed, 15 April 2009 05:30]

Report message to a moderator

Re: Sorting in Merge [message #398142 is a reply to message #398112] Wed, 15 April 2009 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Repeat 1000 times:

THE ONLY WAY TO GET A SPECIFIC ORDER IS TO USE "ORDER BY".


Regards
Michel
Re: Sorting in Merge [message #398151 is a reply to message #398093] Wed, 15 April 2009 06:54 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Along with
Data is not stored in tables in any specific order
Re: Sorting in Merge [message #398155 is a reply to message #398151] Wed, 15 April 2009 07:03 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
cookiemonster wrote on Wed, 15 April 2009 13:54
Along with
Data is not stored in tables in any specific order


Even though this is true, from the insert part of his merge you can see that he uses a sequence.nextval, so the order of insertion MAY be of interest!
Previous Topic: to_number-ora - 01722: invalid number
Next Topic: Getting Terminal Id(Machine Name)in Database Trigger Executing in Application Server Environment
Goto Forum:
  


Current Time: Wed Dec 07 03:11:57 CST 2016

Total time taken to generate the page: 0.12097 seconds