Re: select with Null

On Wed, 22 Nov 2000, Raj Gopalan wrote:

>
> select d.discount_type
> from discount d
> where (d.company_code = 'XX' or
> d.company_code is null)
> and (d.operator_code = 'YY' or
> d.operator_code is null)
>
> Apparently, oracle is not using the index since I am using IS NULL. But the
> functional requirement is such that the query need to consider records even
> if company_code, Operator_code ,... is null.
>
> Any thoughts on how do I modify this query so that the index is being used.
>

A common and simple solution is to choose a value for the column to represent a null value.

'OC_UNKNOWN' could be used to represent NULL operator_code values, 'CC_UNKNOWN' for company_code, etc.

Of course, this may be problematic if an extensive application is already written against these tables.

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address jkstill_at_teleport.com - private Received on Wed Nov 22 2000 - 21:52:01 CST