Vince wrote:
Quote:
>
1)In my people table, persons have a title (Mr, Miss, or whatever).
>
" select distinct person, title from people where title is null or
title is not null "
>
give:
>
PERSON TITLE
0000029 Miss
0000465 Mr
0000469 <-- null
0000624 <-- null
0000900 Miss
>
But when I calculate the number of titles (a lot of possibles types),
the title = null is not took into account. For that, I use " select
count(distinct title) from people where title is null or title is not
null ". How to make it successful?
Nulls have no defined value - not even 'nothing'. Therefore they can
not be counted. To work around this, assign a value to replace the null
using a function like NVL (although you really want to look it up to
make sure you are using the right function) OR count a non-null column
or pseudo-column such as rowid.
Quote:
>
2)Parameter between ' characters
>
My query is:
>
select distinct people.surname, people.forenames, sessions.stage,
sessions.course,
(select distinct title from shared.courses
where sessions.course=shared.courses.course) as titleofcourse
from people, sessions
where people.person=sessions.student and sessions.status='C'
>
I get:
>
SURNAME FORENAMES STAGE COURSE TITLEOFCOURSE
Aggett Stephen Peter James 2 V700 Philosophy <--
'Philosophy'
>
How to put the title of course (getting with a select) between '
characters?
I try without succes to use case or decode(). Have you got an idea?
>
Lean about the CONCAT operator.
Quote:
3)A query on a same table
>
My people table is as following:
>
PERSON SURNAME FORENAMES TITLE KNOWNAS USERNAME EMAIL
>
I would like to check if there is any duplicates, ie two people with
the same surname and the same forenames.
>
I don't want to use a view containing the people table. I think it is
better to make it in a single query. Any idea please?
>
Something like " select * from people as p, people as pp where
p.surname=pp.surname " but which works?
>
Maybe it is something as:
"select p.surname, p.forenames
from people p, (select pp.surname, pp.forenames from people pp)
where p.surname = pp.surname and p.forenames = pp.forenames "
Think about using a grouping-count and eliminate the non-duplicate rows
using a 'having' clause.
HTH
/Hans
