Re: Problem with some SQL queries

Douglas,

> My apologies. I should have read your post more carefully. The code I
> provided previously did the reverse of what you wanted.

No problem. It is very nice to help me.

>>>>Excellent! I would like to improve this query by adding 2 id columns as
>>>>following:
>>>>
>>>>SURNAME   FORENAMES  PERSON1  PERSON2
>>>>McTa      Paul       962      908
>>>>
>>>>where I have in fact 2 id (=preson parameter) for the same person in my
>>>>people table (defined as "PERSON  SURNAME  FORENAMES  TITLE  KNOWNAS
>>>>USERNAME  EMAIL")

> My understanding of your requirements is that you want to do a pivot query
> on SURNAME and FORENAMES for PERSON. My solution is based on 'Pivot Query'
> on pp. 576-7 of "Expert One-on-One Oracle" by Tom Kyte (2001).
>
> SELECT
> surname,
> forenames,
> MAX( DECODE( seq, 1, person, null ) ) AS person1,
> MAX( DECODE( seq, 2, person, null ) ) AS person2
> FROM (
> SELECT
> surname,
> forenames,
> person,
> ROW_NUMBER()
> OVER( PARTITION BY surname, forenames ORDER BY person DESC )
> AS seq
> FROM
> people
> )
> GROUP BY
> surname,
> forenames
> ;

When I execute this query, I obtained too much results, a lot of people instead of only few people.

It seems that the people who are at least twice in my people table (ie same surname and same forenames) have a good seq=1 and seq=2, checking with:

SELECT
   surname,
   forenames,
   person,
   ROW_NUMBER()
   OVER( PARTITION BY surname, forenames ORDER BY person DESC )AS seq FROM people
WHERE surname='MisterWhoIsTwice'

giving:

SURNAME            FORENAMES  PERSON   SEQ
MisterWhoIsTwice   R          96010    1
MisterWhoIsTwice   R          93433    2

But I seems that when I make your all query, it extracts too much people, and not only those who are twice with seq=1 and seq=2. I don't achieve to indicate such a condition because I have some difficulties to use you special view "seq", I think.

>>For the second query:

>>To sump-up, the following query:
>>
>>select distinct people.forenames, people.surname, teachers.seq,
>>case
>>when (teachers.type = 'DL') then 'Director'
>>when (teachers.type = 'D') then 'Director'
>>when (teachers.type = 'L') then 'Lecturer'
>>when (teachers.type = 'S') then 'Supervisor'
>>end as extrarole
>>from teachers, people
>>where teachers.teacher=people.person and (teachers.type like '%D%' or
>>teachers.type like '%L%' or teachers.type like '%S%')
>>order by teachers.seq

> Looks like you will have to put another query around this one in order to
> drop the column. However, if you are using SQL*Plus, you can use the
> following SQL*Plus command to suppress the display of the column SEQ:
>
> COLUMN seq NOPRINT

When I put "column teachers.seq noprint " before my query, a have the following error: "SQL error: ORA-00900: invalid SQL statement". In fact, I use Oracle as the DBMS and SQL is based on SQL/92. I will study it.

Thanks a lot for you help. Received on Thu Feb 19 2004 - 07:24:28 CST