Re: to NULL OR NOT to NULL ? That is the Question.

In article <ianhar.10.000FFA93_at_u09002.skm09.svskt.se>, Ian Harcourt <ianhar_at_u09002.skm09.svskt.se> wrote:
>
>Hi !
> I'm new to this group but I hope this is thew right forum to air my
>problem.
>
>I'm working with Oracle 6.0 and Forms 3.0. I have a problem with a
>SELECT statement.
>
>The table in question has a column that CAN contain a VALUE or NULL.
>
>I wish to incorporate into ONE select statement the possibility to select all
>posts OR just those posts with a certain value, i.e.
>
> and <col_name> like nvl( <in_parameter> ,'%' )
>
> This of course doesn't work because LIKE '%' does not
> return posts whose value is NULL.

you want:

        and nvl(<col_name>, <in_parameter>) like nvl(<in_parameter, '%')

or:

	and (<col_name> like nvl(<in_paramter> , '%') OR
             <col_name> is NULL)

>
>
>To select null one must use
>
> and <col_name> is null
>
>To select ALL VALUES including NULL one doesn't need any condition.
>However, I am trying to be clever and minimize the number of select statements
>and write Dynamic SQL.
>
>It feels as if I need some sort of conditional OR, which of course doesn't
>exist. Some how I must enable/disable an IS NULL condition or select all posts
>via another method.
>
>Any Ideas !
>
>
>Last time I had this problem I was forced to write two SELECTs, this time it
>would be helpful to advoid this and beat the system.
>
>
>Best Wishes
>
>Ian H
>
>ianhar_at_u09002.skm09.svskt.se
>Tel. INT + 46 498 292239
>Fax INT + 46 498 249263

Kevin Toepke Received on Sat Dec 03 1994 - 00:40:56 CET