Re: SELECT question.

Hi Robert.
Thanks for the solution. It works in SQL*plus in MS Query etc.

Do you have any idea, why I canNOT build a record group with the same SELECT ?
Alert says something like "Cannot create record group (check your query)"

By the way.
I have put my original question in a another (czech) group oracle_at_nic.zcu.cz (which is a mailing list actualy). Do I have your permission to forward your answer to that group (with your name and all) ?

Regards Tony.

rtproffitt_at_my-deja.com wrote:

> Tony,
> Try the following approach.....
>
> In article <38D0C64F.14B4820C_at_lund.mail.telia.com>,
> plant.eng_at_lund.mail.telia.com wrote:
> > I have following problem:
> > Table DEPT columns DEPT_ID, DEPT_NAME
> > Table EMP, columns EMP_ID, EMP_NAME, DEPT_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
> >
>
> SQL> create table bob
> 2 (dept varchar2(10),
> 3 deptid varchar2(10),
> 4 emp varchar2(10),
> 5 empid varchar2(10));
> [You can adapt this to your join....]
>
> SQL> select * from bob;
>
> DEPT DEPTID EMP EMPID
> ---------- ---------- ---------- ----------
> DEPTA A Bill 877
> DEPTA A Julie 766
> DEPTA A Johann 655
> DEPTB B Karen 544
> DEPTB B Lisa 433
> DEPTB B Radu 211
>
> select
> Decode(DataTbl.RowCnt,
> MinTbl.MinRow, DataTbl.Dept,
> NULL) DeptDisplay
> ,DataTbl.Dept
> ,DataTbl.Emp
> ,DataTbl.DeptId
> ,DataTbl.EmpId
> From
> (-- get rownumbers in sorted order
> select Rownum RowCnt, dept, emp, deptid, empid
> From (
> select Dept, emp, deptid, empid
> from bob
> group by dept, emp, deptid, empid) a
> ) DataTbl,
> --
> (-- get the min pos of each major key break
> select Min(Rowcnt) MinRow, Dept
> From
> (select Rownum RowCnt, dept
> From (
> select Dept, emp, deptid, empid
> from bob
> group by dept, emp, deptid, empid) a
> ) b
> Group by Dept
> ) MinTbl
> Where DataTbl.Dept = MinTbl.Dept
> Order by
> DataTbl.Dept,
> DataTbl.Emp,
> DataTbl.Deptid,
> DataTbl.Empid
> /
>
> DEPTDISPLA DEPT EMP DEPTID EMPID
> ---------- ---------- ---------- ---------- ----------
> DEPTA DEPTA Bill A 877
> DEPTA Johann A 655
> DEPTA Julie A 766
> DEPTB DEPTB Karen B 544
> DEPTB Lisa B 433
> DEPTB Radu B 211
>
> 6 rows selected.
>
> ==== Explanation ====
> See numbers in text below explanation
> 1. Get the data ordered properly
> 2. Add row numbers
> 3. Get Rownumber of beginning of each Dept block
> 4. Select all the data and add rownumbers
> to proper order
> 5. Join all the data with the Department minimum
> Row numbers.
> 6. Display all the data, and a formula
> for displaying either the department,
> or NULL.
> When the row number of the data matches
> the min row of the dept, the we are
> at the first row of new dept, so display
> the dept, otherise display null.
> 7. Whole output is ordered properly.
> Don't use the displayed deptartment to order,
> but use the real department.
>
> select
> 6. xxx, a, b, c, ...
> From
> (4. select Rownum xxxxxx
> From ( 1. xxxx
> ) a
> ) DataTbl,
> --
> (3. xxx Min xxx
> From (2. xxx
> From ( 1. xxx ) a
> ) b
> Group by Dept
> ) MinTbl
> Where 5. xxx=xxx
> Order by 7. a,c,b,d
>
> Hope that helps,
> Robert Proffitt
> Beckman Coulter
> Brea California
> RTProffitt AT beckman DOT com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Mar 17 2000 - 03:53:24 CST