From: hforbric@yahoo.net   
      
   Vince wrote:   
   >   
   > 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.   
      
   >   
   > 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.   
      
   > 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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)