Re: SELECT question once again.

From: Mike Dwyer <dwyermj_at_co.larimer.co.us>
Date: Fri, 17 Mar 2000 10:24:51 -0700
Message-ID: <zXtA4.7$HF2.3422_at_wdc-read-01.qwest.net>

Thank you for a great challenge, Tony! SQL*Plus would let you do this in a heartbeat with
BREAK ON DEPT NODUP
SELECT DEPT, EMP, EMP.DEPT_ID, EMP_ID
FROM EMP, DEPT
WHERE EMP.DEPT_ID = DEPT.DEPT_ID But that is not available in the SQL that builds a record group for an LOV. You need to get real creative to do what you are asking. My approach uses a view:

create or replace view FIRST_EMPS as
select DEPT_ID, min( EMP_ID) EMP_ID
from EMP
group by DEPT_ID;

In forms, use this query:
select
 decode( EMP_ID, fe.EMP_ID,DEPT.DEPT, null) DEPT,

 EMP.EMP,
 EMP.DEPT_ID,
 EMP.EMP_ID

from EMP, FIRST_EMPS fe, DEPT
where EMP.DEPT_ID = fe.DEPT_ID
and DEPT.DEPT_ID = fe.DEPT_ID
order by EMP.DEPT_ID, EMP.EMP_ID;

You will probably need to modify the query to concatenate some of the columns for display purposes in the LOV, but that is another exercise.

Also, the view and query above will be sorted (numerically) by dept and employe id. An alpha sort is possible:

create or replace view FIRST_EMPS as
select DEPT_ID, min( EMP) EMP
from EMP
group by DEPT_ID;

select
 decode( EMP, fe.EMP,DEPT.DEPT, null) DEPT,

 EMP.EMP,
 EMP.DEPT_ID,
 EMP.EMP_ID,

 DEPT.DEPT SORTDEPT
from EMP, FIRST_EMPS fe, DEPT

where EMP.DEPT_ID  = fe.DEPT_ID
and   DEPT.DEPT_ID = fe.DEPT_ID
order by DEPT.DEPT, EMP.EMP;

(I used production tables in my database to develop and debug this, then converted the code back to DEPT and EMP. I may have erred in the conversion, but the concept should work for you.)

Good luck!

Tony <TonyS_at_Lords.com> wrote in message news:38D18183.81346A8F_at_Lords.com...
> I am sorry to confuse you.
> Forget Oracle demo EMP, DEPT
>
> Lets have table DEPT with columns DEPT_ID, DEPT
> and a few values
> DEPT1_ID, DEPT1
> DEPT2_ID, DEPT2
> ...
>
> Lets have table EMP with columns EMP_ID, EMP, DEPT_ID
> and a few values
> EMP1_ID, EMP1, DEPT1_ID
> EMP2_ID, EMP2, DEPT1_ID
> EMP3_ID, EMP3, DEPT1_ID
> EMP4_ID, EMP4, DEPT2_ID
> EMP5_ID, EMP5, DEPT2_ID
> ...
>
> Can you help me now?
>
> Regards Tony.
>
> Sanrenkur wrote:
>
> > >TONY WROTE:
> >
> > >My previous question was not answered so I'll try to
> > >make it more clear. The best way to see the problem is,
> > >to look at this message with "fix pitch" font, like Courier.
> > >
> > >I have following problem:
> > >Table DEPT columns DEPT_ID, DEPT_NAME
> > >Table EMP, columns EMP_ID, EMP_NAME, DEPT_ID
> > >
> > >I need to build a LOV listing employees ordered by department.
> > >I can easily build a view (record group) giving:
> > >DEPT1 EMP1 DEPT1_ID EMP1_ID
> > >DEPT1 EMP2 DEPT1_ID EMP2_ID
> > >DEPT1 EMP3 DEPT1_ID EMP3_ID
> > >DEPT2 EMP4 DEPT2_ID EMP4_ID
> > >DEPT2 EMP5 DEPT2_ID EMP5_ID
> > >
> > >What I want instead is:
> > >DEPT1 EMP1 DEPT1_ID EMP1_ID
> > > EMP2 DEPT1_ID EMP2_ID
> > > EMP3 DEPT1_ID EMP3_ID
> > >DEPT2 EMP4 DEPT2_ID EMP4_ID
> > > EMP5 DEPT2_ID EMP5_ID
> > >
> > >Is there anyone who can give me a hint how to build a view like this
> > >or any reference, where I can find similar solution?
> > >
> > >Thanks Tony.
> > >
> > >Tony
> > Your listed output is very confusing and does not correspond to any
table
> > columns.
> > Try listing the actual values you would like to see in your output.
> > Sandy
> >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
>
Received on Fri Mar 17 2000 - 18:24:51 CET