Re: Problem with some SQL queries

Vince,

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

> >>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

The inline view returns the three (3) columns ( SURNAME, FORENAMES, and PERSON ) along with a sequence number (SEQ). This sequence number is generated by the analytic function, ROW_NUMBER(). The OVER clause says that I want to group (or partition) the table PEOPLE by SURNAME and FORENAMES, and then to order rows within each such group (or partition) by PERSON in descending order. Thus, SEQ #1 will be assigned for the maximum value of PERSON. (I assume that you want a descending order for PERSON as indicated by sample output).

The outer query takes the value of SEQ to determine which column ( PERSON1 or PERSON2 ) gets the value of PERSON from the inline view. The MAX function along with the GROUP BY clause ensures that there is only one row produced for each distinct pair of values for SURNAME and FORENAMES.

> 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
>
> gives:
>
> FORENAMES SURNAME SEQ EXTRAROLE
> Chris Borg 0 Director
> Alex Stel 1 Director
> Hug Hann 1 LinkMan
>
> >>However, "order by teachers.seq" seems to need that I also select
> >>"teachers.seq".
>
> > This is not true for Oracle. Just remove the column seq from the SELECT
> > list.
>
> If I simply delete "teachers.seq" in the select, I obtain an error: "SQL
> error: ORA-01791: not a SELECTed expression"
>
> using:
>
> select distinct people.forenames, people.surname,
> 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
>
> Strange, isn't it? How to make it in a single select?
>
> Thanks by advance.

Again my apologies. My error this time was not noticing the SELECT DISTINCT. The error message you gave pointed this out to me. If you have an ORDER BY clause, then, in this case, you must include TEACHERS.SEQ in the SELECT list.

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

I hope my reading comphresion has improved sufficiently for this to be of use to you.

Douglas Hawthorne Received on Thu Feb 19 2004 - 05:35:13 CST