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

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.

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 Received on Fri Dec 02 1994 - 21:58:37 CET