ROWID / subselect problem

From: Mark <mark.harris.spam.begone_at_ukonline.co.uk.spam.begone>
Date: Thu, 30 Jan 2003 09:51:27 -0000
Message-ID: <3e38f5a1$0$2565$afc38c87@news.easynet.co.uk>

Hello,

I have a problem which is baffling me somewhat.

I have a query which includes a join to a table which might have more than 1 row for the record in question. Therefore, I've tried to eliminate duplicate rows by adding in a sub-select which only brings back the 1st row, like this:

SELECT ...
FROM   ...
WHERE  ...

AND table.rowid =

    (SELECT table2.rowid

     FROM    table2
     WHERE   table2.column = table.column
     AND     rownum = 1

    )

This works (i.e. it removes all duplicate rows) for all but 2 cases, where no rows are returned. These rows *never* had duplicates in the table, but a single row, so they shouldn't be affected by the sub-select.

If I cut the sub-select out and hardcode the join condition, it works fine and brings back a rowid. If I hardcode this rowid into my original query, it works and brings back 1 row.

Therefore, I'm muchly confused why this should happen.

Thanks for any assistance.

Mark Received on Thu Jan 30 2003 - 03:51:27 CST