Re: subquery returning no rows in WHERE clause

Assuming my experience from Oracle7 translates into Oracle8, I'd suggest you try one of two things.

1. Since your subquery can, at most, return 1 row (or else there will be an error), replace SELECT ID ... with SELECT NVL(MAX(ID),Table1.IDCol+1). This is a bit of a kludge, but should work. The logic is that MAX(ID) will always equal ID, since there is one row. If there is no row, then MAX(ID) will be null and the NVL() function will return a value not equal to IDCol.
2. Use a not exists.

SELECT TextCol FROM Table1
WHERE NOT EXISTS (select 'x' FROM IDTable WHERE IDValue = '...' AND ID = Table1.IDCol)

Hope one of these works. It's hard to tell without knowing your exact problem.

• Matt

Thomas Briggs wrote in message <37568EB2.45BF477E_at_sane.com>...
>Is there any way in Oracle8 to have a not-equals comparison against a
>subquery return rows when the subquery returns no rows?
>
>Take the following query:
>
>SELECT TextCol FROM Table1
>WHERE IDCol <> (SELECT ID FROM IDTable
>WHERE IDValue = '...')
>
>When there is no matching record in the IDTable this returns no rows, as
>the subquery returns no
>rows. Logically, however, this is incorrect - everything is not equal
>to nothing! Is there any
>way (without an embedded PL/SQL function) to have this return the
>logically correct rows?
>
> Thanks
> -Tom
Received on Fri Jun 04 1999 - 18:47:03 CDT