Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed in Select (Oracle 9.0.2.0.1, Unix)
Help Needed in Select [message #337214] Wed, 30 July 2008 03:11 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi,

I need help in writing a SQL to obtain the desired results. I tried rownum, union, decode etc. but not suceeded. Kindly help.
Below is the scenario.
CREATE TABLE TEST_1
(A number, B number);

CREATE TABLE TEST_2
C number, D number);

CREATE TABLE TEST_3
E number, F number);

insert into test_1 values(11, 12);
insert into test_1 values(21, 22);
insert into test_1 values(31, 32);
insert into test_2 values(13, 14);
insert into test_2 values(23, 24);
insert into test_2 values(33, 34);
insert into test_3 values(15, 16);
insert into test_3 values(25, 26);
insert into test_3 values(35, 36);

select * from test_1;

         A          B
---------- ----------
        11         12
        21         22
        31         32


select * from test_2;
         C          D
---------- ----------
        13         14
        23         24
        33         34

select * from test_3

         E          F
---------- ----------
        15         16
        25         26
        35         36


DESIRED OUTPUT
--------------

         A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
        11         12         13         14         15         16
        21         22         23         24         25         26
        31         32         33         34         35         36


Thanks
Amit
Re: Help Needed in Select [message #337216 is a reply to message #337214] Wed, 30 July 2008 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to first define the order for each table.
Then number each row for this order (see row_number function).
Finally join the 3 tables against the same row_number.

Regards
Michel

[Updated on: Wed, 30 July 2008 03:18]

Report message to a moderator

Re: Help Needed in Select [message #337221 is a reply to message #337216] Wed, 30 July 2008 03:23 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi Michel,

Thanks for the quick reply.
But I didn't get this. If possible can you please explain with example.

Quote:
You have to first define the order for each table.


Regards
Amit
Re: Help Needed in Select [message #337223 is a reply to message #337221] Wed, 30 July 2008 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is your data, in which order you want to appear in the result.
Or if you prefer, why (11,12) appears along with (13,14) and (15,16) and not with (23,24) and (35,36)?

Regards
Michel
Re: Help Needed in Select [message #337225 is a reply to message #337223] Wed, 30 July 2008 03:38 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Thanks Michel !!
I got it finally Smile
Re: Help Needed in Select [message #337231 is a reply to message #337225] Wed, 30 July 2008 04:06 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to post your query for future readers.

Regards
Michel
Previous Topic: Query
Next Topic: Why is the job not running ?
Goto Forum:
  


Current Time: Fri Dec 09 02:18:00 CST 2016

Total time taken to generate the page: 0.18360 seconds