Re: NULL values in IN condition

On 12 dic, 02:53, Peter Nilsson <ai..._at_acay.com.au> wrote:
> "Álvaro G. Vicario" <alvaroNOSPAMTHA..._at_demogracia.com> wrote:
>
>
>
> > I want to obtain all the possible values of a VARCHAR2(1)
> > column named FOO, sort them (either ASC or DESC) and finally
> > fetch to two top-most rows. NULL values must be listed on
> > top (ASC) or bottom (DESC). So I've written these queries:
>
> > SELECT * FROM (
> > SELECT DISTINCT FOO
> > FROM TABLE
> > ORDER BY FOO NULLS FIRST
> > ) WHERE ROWNUM<=2
> > -- Returns: NULL, 'A'
>
> > SELECT * FROM (
> > SELECT DISTINCT FOO
> > FROM TABLE
> > ORDER BY FOO DESC NULLS LAST
> > ) WHERE ROWNUM<=2
> > -- Returns: 'Y', 'Z'
>
> > So far so good. Now I'd like to nest this subquery in an
> > IN condition:
>
> > SELECT *
> > FROM ANOTHER_TABLE
> > WHERE FOO_FK IN (.......)
>
> > However, the IN condition always filters out NULL values.
> > Something like FOO IN (NULL, 'A', 'B') is FALSE even when
> > FOO is null; apparently, because FOO=NULL is not TRUE.
> > But I need to consider rows with NULLs.
>
> > I don't know before hand if there're NULLs in my "top 2",
> > so...
>
> > WHERE FOO_FK IS NULL OR FOO_FK IN (.......)
>
> > is not an option.
>
> > Can you think of any trick to overcome this?
>
> > The server runs Oracle 9.2.0.1.0
>
> > -----
>
> > Just before hitting "Send" I came to this idea:
>
> > SELECT *
> > FROM ANOTHER_TABLE
> > WHERE NVL(FOO_FK, 'xxxxxxxxx') IN (
> > SELECT NVL(FOO, 'xxxxxxxxx') FROM (
> > SELECT DISTINCT FOO
> > FROM TABLE
> > ORDER BY FOO NULLS FIRST
> > ) WHERE ROWNUM<=2
> > )
>
> > It works but... It looks so ugly! :-?
>
> with
> tofu as (select foo
> from (select distinct foo
> from table
> order by foo nulls first)
> where rownum<=2)
> select *
> from another_table t
> where exists
> (select 1
> from tofu tf
> where tf.foo = t.foo
> or tf.foo is null and t.foo is null)
>
> --
> Peter

Why on earth could someone define a column as varchar2(1)??

Cheers.

Carlos. Received on Fri Dec 12 2008 - 02:10:30 CST