sql append [message #354782] |
Tue, 21 October 2008 03:55  |
gaikwadrachit
Messages: 33 Registered: June 2007 Location: mumbai
|
Member |
|
|
hi
i have two queries
select emp_name from emp
where emp_id = 13;
emp_name
ra
sa
ds
ds
select dept_no from dept where deptname = 'account'
dept_no
16
both tables dont have any relatio between them
i want an output when running both the queries together
in short i want to dispalay both columns side by side
emp_name dept_no
ra 16
sa
ds
ds
|
|
|
|
|
Re: sql append [message #354792 is a reply to message #354789] |
Tue, 21 October 2008 05:07   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Your requirement is nonsensical.
Can you tell us what the problem that you are trying to solve is - that way we can probably help you.
|
|
|
|
Re: sql append [message #354893 is a reply to message #354782] |
Tue, 21 October 2008 20:49  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
The logic behind what you are asking is called MERGE THEORY. You might try a google about it and do some reading for details.
You want scan two unrelated lists at the same time and put them together. The first thing to know is that you cannot merge two unrelated lists. This means you will first have to make the two lists compatible somehow. In cases where there is nothing in common this means you have to make it up. This of course is part of what makes your particular problem interesting: the need to lie to the Oracle software to make it do what you want (hehe)...
Cartesion product though related to merge is not the same thing as I think was requested. Cartesion product means each member of X is paired to each member of Y so 10x3 = 30. In a merge as is described by the OP (at least what I think I understand), the answer should be 10x3 = 10 because the longest list is 10 rows long. This means that "do a join" ain't enough to cut it. There is a lot of work involved.
The issues you will have to address in this exercise are:
Quote: | 1) how to make unrelated lists related
2) how to merge lists of unequal length to ensure nothing gets lost
| Here is a demonstration of the theory:
with
list_1 as (
select 'a' aletter from dual union all
select 'b' from dual union all
select 'c' from dual
)
, list_2 as (
select table_name
from all_tables
where rownum < 10
)
, augmented_list_1 as (
select rownum rowno,a.*
from list_1 a
)
, augmented_list_2 as (
select rownum rowno,a.*
from list_2 a
)
, list_key as (
select rownum rowno
from dual
connect by level <= greatest(
(select count(*) from augmented_list_1)
,(select count(*) from augmented_list_2)
)
)
select a.aletter,b.table_name
from augmented_list_1 a
,augmented_list_2 b
,list_key
where list_key.rowno = a.rowno(+)
and list_key.rowno = b.rowno(+)
/
Notice the following:
Quote: | 1) we started with two unrelated arbitrary lists
2) we made the two lists compatible by inventing a meaningless key that we knew would be unique and common between the two lists (eg. both lists get their fake key from the same source)
3) we determine the length of the longest list from which we are able to generate a list of known fake keys in use
4) now we outer-join both lists
|
All this work is because we are doing evertying in sql alone. There may be more efficient way to do this merge in sql alone, but this method is good for teaching the pieces used.
In plsql we can run dualing cursors simultaneously and just pair up rows as they come off the cursors.
set serveroutput on
declare
cursor c1 is
select 'a' aletter from dual union all
select 'b' from dual union all
select 'c' from dual
;
cursor c2 is
select table_name
from all_tables
where rownum < 10
;
r1 c1%rowtype;
r2 c2%rowtype;
begin
open c1;
open c2;
fetch c1 into r1;
fetch c2 into r2;
loop
if c1%found and c2%found then
dbms_output.put_line(r1.aletter||','||r2.table_name);
elsif c1%found then
dbms_output.put_line(r1.aletter||',');
elsif c2%found then
dbms_output.put_line(','||r2.table_name);
else exit;
end if;
if c1%found then fetch c1 into r1; end if;
if c2%found then fetch c2 into r2; end if;
end loop;
close c2;
close c1;
end;
/
Output of the above code for those without access to sqlplus:
A TABLE_NAME
- ------------------------------
a ICOL$
b CON$
c UNDO$
PROXY_ROLE_DATA$
FILE$
UET$
IND$
SEG$
COL$
9 rows selected.
a,ICOL$
b,CON$
c,UNDO$
,PROXY_ROLE_DATA$
,FILE$
,UET$
,IND$
,SEG$
,COL$
PL/SQL procedure successfully completed.
So, examine these coding samples, do the reading people have suggested, then adapt the theory to whatever need you have.
Good luck, Kevin
|
|
|