Re: SQL Question - Select clause within Select List

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/29
Message-ID: <8jfsc4$fiu$1@nnrp1.deja.com>

In article <8jduf4$2hi$1_at_nnrp1.deja.com>,   logo_palanisamy_at_hotmail.com wrote:
> I have a SQL question for the table structure given below.
>
> SQL> desc dept
> Name Null? Type
> ----------------------------------------- --------

---

> DEPTNO NOT NULL NUMBER(2)
> DNAME VARCHAR2(14)
> LOC VARCHAR2(13)
>
> SQL> desc emp
> Name Null? Type
> ----------------------------------------- --------

---

> EMPNO NOT NULL NUMBER(4)
> ENAME VARCHAR2(10)
> JOB VARCHAR2(9)
> MGR NUMBER(4)
> HIREDATE DATE
> SAL NUMBER(7,2)
> COMM NUMBER(7,2)
> DEPTNO NUMBER(2)
>
> SQL> l
> 1 select dname, ename from dept, emp
> 2 where dept.deptno = emp.deptno
> 3* order by dname
>
> DNAME ENAME
> -------------- ----------
> ACCOUNTING CLARK
> ACCOUNTING KING
> ACCOUNTING MILLER
> RESEARCH SMITH
> RESEARCH ADAMS
> RESEARCH FORD
> RESEARCH SCOTT
> RESEARCH JONES
> SALES ALLEN
> SALES BLAKE
> SALES MARTIN
> SALES JAMES
> SALES TURNER
> SALES WARD
>
> I am not interested in the above output. I want the output to be like
> the one given below.
>
> DNAME ENAME(S)
> -------------- -------------------------------------------
> ACCOUNTING CLARK, KING, MILLER
> RESEARCH SMITH, ADAMS, FORD, SCOTT, JONES
> SALES ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD
>
> I thought the following query might help. But it fails.
>
> SQL> select b.dname, (select a.ename from emp a where a.deptno =
> b.deptno)
> 2 from dept b;
> select b.dname, (select a.ename from emp a where a.deptno = b.deptno)
> *
> ERROR at line 1:
> ORA-01427: single-row subquery returns more than one row
>
> How do I concatenate the names and make it to one big ENAMES column?
> Do I have to pretty much write a function that
> returns all the employee names for a given department and use it in
 the
> select statement as given below?
>
> select dname, enames_fn(deptno) from dept;
>
> Are the better alternatives?
>
> Thanks in advance,
> Logo Palanisamy
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Here is a possible solution:

set serveroutput on size 1000000
declare

        --
        -- Get the department name and department id
        --
        cursor get_dname is
        select deptno, rpad(dname,15) dname
        from dept
        order by deptno;
        --
        -- Get the employee count by department
        --
        cursor get_emp_ct (dno in number) is
        select count(*) emp_ct
        from emp
        where deptno = dno;
        --
        -- Get the employee names by department
        --
        cursor get_emp (dno in number) is
        select ename
        from emp
        where deptno = dno
        --
        -- Counter
        --
        e_ct number:=0;
begin
        --
        -- Output headers
        --
        dbms_output.put_line('DNAME           EMPLOYEES');
dbms_output.put_line('-------------------------------------------------
---------------');
        --
        -- Loop through departments
        --
        for drec in get_dname loop
                --
                -- Output department name
                --
                dbms_output.put(drec.dname||'   ');
                --
                -- Get department employee count
                --
                -- This is a 'trigger' for the commas
                -- in the employee list
                --
                open get_emp_ct(drec.deptno);
                fetch get_emp_ct into e_ct;
                close get_emp_ct;
                --
                -- Loop through employees
                --
                for erec in get_emp(drec.deptno) loop
                        --
                        -- If more than one name put a comma
                        --
                        if e_ct > 1 then
                        dbms_output.put(erec.ename||', ');
                        else
                        --
                        -- One name left, or only one name
                        -- available
                        --
                        -- Don't output a comma
                        --
                        dbms_output.put_line(erec.ename);
                        end if;
                        --
                        -- Decrement counter for each name
                        -- output
                        --
                        e_ct := e_ct - 1;
                end loop;
        end loop;

end;
/

This will return the data formatted as you desire, and it runs fairly quickly. This is not the only solution, just one I came up with on the spur of the moment.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Jun 29 2000 - 00:00:00 CDT