Re: Fw: Problem with some SQL queries

Since you went to the trouble of posting in the right place I'll answer, even though Hans answereed you in the defunct group.

Vince <vince_at_nospam.net.invalid> wrote in message news:<4033c83e$0$28103$626a14ce_at_news.free.fr>...
> Fw from comp.databases.oracle
>
> I have several questions about queries I make into my database. I will
> explain you. Could you please help me?

We try to be helpful.

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

This is the same as
select distinct person, title from people ;

But why are you using DISTINCT??

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

If you read the manual page on COUNT(), you would know that NULL is not counted.

> ... For that, I use " select
> count(distinct title) from people where title is null or title is not
> null ". How to make it successful?

convert NULL to a value that COUNT() will use. The function for this is NVL(), the null value function. So
select count( distinct NVL(title,'No Title') from people ; will work.
just to convince youself, try running:
select distinct NVL(title,'No Title' from people ;
>
> 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?

Just so I'm sure, you get Philosophy but you want 'Philosophy'?

use the concatenation operator || to combine the column with hardcoded strings.
for example

     'X' ||title
in the select clause would return
XPhilosophy
I'll leave the specifics of your case to you with the hint that you'll need to deal with single quote differently.

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

This is one of VERY FEW casees where the ROWID pseudocolumn is useful. modifying your first version like this:
select * from people as p, people as pp where

   p.surname=pp.surname AND p.ROWID <> pp.ROWID ; should get you on your way to success.

HTH,
  Ed Received on Thu Feb 19 2004 - 13:49:06 CST