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

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.

> 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 !

How about:

       and decode(:in_parameter,NULL,'1',col_name) like
           decode(:in_parameter,NULL,'1',:in_parameter)

If you specify a parameter, this will return only those rows where the col_name is like the in_parameter. Otherwise, it will return all rows. (Including those with null values)

I use this method quite a lot in SQL*Reportwriter

> 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

--
 
John Blackburn                                          Phone: +61 7 2534634
jb2_at_qdot.qld.gov.au                                     Fax:   +61 7 8541194