Home » SQL & PL/SQL » SQL & PL/SQL » sql append
sql append [message #354782] Tue, 21 October 2008 03:55 Go to next message
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 #354788 is a reply to message #354782] Tue, 21 October 2008 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As already requested to you:
read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and post what you tried.
Clue: join on rownum.

Regards
Michel
Re: sql append [message #354789 is a reply to message #354782] Tue, 21 October 2008 04:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try Cartesian Join

Thumbs Up
Rajuvan.
Re: sql append [message #354792 is a reply to message #354789] Tue, 21 October 2008 05:07 Go to previous messageGo to next message
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 #354880 is a reply to message #354782] Tue, 21 October 2008 15:29 Go to previous messageGo to next message
shettypravs
Messages: 9
Registered: August 2008
Junior Member
yep...as Rajuvan mentioned please try to go thru the topic "Joins"
Re: sql append [message #354893 is a reply to message #354782] Tue, 21 October 2008 20:49 Go to previous message
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
Previous Topic: package athat automatically inserts error code n message
Next Topic: Find a row with NULL Value
Goto Forum:
  


Current Time: Tue Feb 11 13:37:08 CST 2025