Re: SQL Report

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Tue, 06 May 2003 16:15:13 GMT
Message-ID: <MPG.19217f67491f83d898976e@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!

-- 
/Karsten
DBA > retired > DBA

Received on Tue May 06 2003 - 11:15:13 CDT