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.
Received on Wed May 07 2003 - 10:53:51 CDT
