Re: Problem with some SQL queries

From: Vince <vince_at_nospam.net.invalid>
Date: Thu, 19 Feb 2004 01:59:02 +0000
Message-ID: <40341865$0$28105$626a14ce@news.free.fr>

Douglas,

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

> One suggestion would be:
> SELECT surname, forenames, person1 AS person FROM people
> UNION
> SELECT surname, forenames, person2 AS person FROM people
> ;

NB: I think you would say "person AS person1" since I have only person in my people table.

It is very strange, because I can obtain the first wished columns (surname and forenames) with:

select surname, forenames from
(
select surname, forenames, person AS person1 FROM people union
select surname, forenames, person AS person2 FROM people )
group by surname, forenames
having count(*)>1

but when I add the 2 id columns to obtain what I want, I have the following error "SQL error: ORA-00904: invalid column name" certainly because I ask "person1", "person2" which are not reconnaize by the first select

using:

select surname, forenames, person1, person2 from (
select surname, forenames, person AS person1 FROM people union
select surname, forenames, person AS person2 FROM people )
group by surname, forenames
having count(*)>1

How to manage it, plaese?

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. Received on Wed Feb 18 2004 - 19:59:02 CST