Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.1922d4bb17d82104989773_at_news.la.sbcglobal.net>...
> afilonov_at_yahoo.com said...
> > Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.19217f67491f83d898976e_at_news.la.sbcglobal.net>...
> > > nospam_at_spam.com said...
> > > > Hi..All,
> > > >
> > > > I have 2 tables employee (employeeid), dept (dept_id, ,employeeid).
> > > > I want to get a report in the following format
> > > >
> > > > <dept_id1>
> > > > employeeid1
> > > > employeeid2
> > > > ...
> > > > ...
> > > >
> > > > <dept_id2>
> > > > employeeidXX
> > > > employeeidYY
> > > > ....
> > > > ...
> > > > ..
> > > >
> > > >
> > > > Is this possible to do it in a single SQL.
> > > >
> > > > Regards,
> > > >
> > > > P
> > > Kinda hard to do in a single SQL; relatively simple in PL/SQL. In
> > > sqlplus, type this:
> > >
> > > set serveroutput on size 100000
> > >
> > > declare
> > > cursor c_dept is select dept_id, employeeid from dept;
> > > cursor c_emp(nin_emp_id number) is
> > > select employeeid from employee where employeeid = nin_emp_id;
> > > --
> > > n_dept_id number;
> > > n_emp_id number;
> > > begin
> > > dbms_output.enable(1000000);
> > > --
> > > open c_dept;
> > > loop
> > > fetch c_dept into n_dept_id, n_emp_id;
> > > dbms_output.put_line(chr(10)||to_char(n_dept_id,'99999'));
> > > --
> > > open c_emp(n_emp_id);
> > > loop
> > > fetch c_emp into n_emp_id;
> > > dbms_output.put_line(to_char(n_emp_id,'99999'));
> > > end loop;
> > > close c_emp;
> > > end loop;
> > > close c_dept;
> > > end;
> > >
> > > Not responsible for typos!
> >
> >
> > This is an example of the most inefficient code you can make of it.
> > Joins are better left to SQL engine, it knows how to join tables
> > better than you.
> >
> You are absolutely right!!! I don't pretend to be able to create a
> PL/SQL program that can outperform a straight SQL statement.
>
> HOWEVER, if you read what the OP asked for, you'll see that they wanted
> a VERTICAL listing of columns. A join will give you a HORIZONTAL listing
> of columns.
The listing of columns is a matter of formatting. See my other post in
this thread.
Received on Wed May 07 2003 - 17:33:47 CDT
