At 07:08 AM 5/04/2004 +0000, you wrote:

>Hi again,
>
>Can you tell me why when we use "IN" instead of " = " in a sub-select
>it returns all the rows and not only matching rows ???
>
>Exemple :
>
>SELECT
>
>NUMVOL, DATEVOL, NUMAVION, IDPILOTE
>from affectation
>where IDPILOTE = (select first :n skip (:n * :p - :n) IDPILOTE from
>pilote)
>
>It works !!! returns 3 rows.
>
>SELECT
>
>NUMVOL, DATEVOL, NUMAVION, IDPILOTE
>from affectation
>where IDPILOTE IN (select first :n skip (:n * :p - :n) IDPILOTE from
>pilote).
>
>Don't work returns all the rows...

Because SELECT FIRST does return all rows, internally. When you use it
dynamically as a main statement, all that happens is it gives you the first
row in the set and swallows the rest.

Lesson: Don't use SELECT FIRST in a subquery. In fact, don't make an
IN() subquery out of any query specification that can't resolve logically
to an EXISTS() predicate....

/heLen