Re: SQL Report

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.

-- 
/Karsten
DBA > retired > DBA