Re: Problem with some SQL queries

Vince,

"Vince" <vince_at_nospam.net.invalid> wrote in message news:4034b90a$0$28119$636a15ce_at_news.free.fr...

> When I execute this query, I obtained too much results, a lot of people
> instead of only few people.

SNIP.
> But I seems that when I make your all query, it extracts too much
> people, and not only those who are twice with seq=1 and seq=2. I don't
> achieve to indicate such a condition because I have some difficulties to
> use you special view "seq", I think.

To solve this, I should have added the following restriction to the outer query:

   HAVING MAX( seq ) > 1

This will only select rows that have at least two occurrences of the same values for SURNAME and FORENAMES because rows with only one occurrence of a particular combination of SURNAME and FORENAMES will not have a value of SEQ greater than 1

The full query would now be:
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
   HAVING
      MAX( seq ) > 1

> > 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
>
> When I put "column teachers.seq noprint " before my query, a have the
> following error: "SQL error: ORA-00900: invalid SQL statement". In fact,
> I use Oracle as the DBMS and SQL is based on SQL/92. I will study it.
>
> Thanks a lot for you help.

According to the Oracle Database Error Messages documentation, ORA-00900 invalid SQL statement
Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.
Action: Correct the syntax or install the Procedural Option.

When I enter SQL*Plus, I get the following output because I am running Oracle 9.2 on WinXP:

C:\>sqlplus test_user_at_testsrvr

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Feb 20 10:25:22 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> COLUMN seq NOPRINT
SQL> SELECT ... Could you post what you get by using similar commands?

Douglas Hawthorne Received on Thu Feb 19 2004 - 17:45:31 CST