Re: Pro*COBOL and (possible) NULL host variabe in WHERE condition

Chris wrote:
> I have tableA, defined as:
>
> field1 varchar2(10),
> field2 varchar2(10),
> field3 varchar2(10)
>
> I have host variables defined as:
>
> v1 pic x(10) varying
> v2 pic x(10) varying
> v3 pic x(10) varying
>
> If insert the following record:
>
> v1-len=5 v1-arr=Hello
> v2-len=0 v2-arr=<spaces>
> v3-len=5 v3-arr=World
>
> I end up with this in the database:
>
> field1=Hello
> field2=<null>
> field3=World
>
> However, if I set the host variables exactly the same and try this:
>
> select
> 'I found it'
> from
> tableA
> where
> field1 = :v1
> and
> field2 = :v2
> and
> field3 = :v3
>
> I am getting a 1403 SQLCODE returned.
>
> I know that I cannot use indicator variables in WHERE conditions to
> search for NULLs, so what is the fix? I can't create a separate WHERE
> condition for each "NULL possibility" - in this small example alone I
> woul dneed 8 different possible SELECT statements.
>
> I know it must be something fairly obvious, but its not jumping out
at
> me right now and my eyes hurt from trying to wade through the Oracle
> docs.
>
> As always, any thoughts/suggestion/solutions are most appreciated.
>
>
> Thanks,
> Chris

I knew that I would find something as soon as I posted this message.

One solution (as documented in Oracle's 9i documentation for Pro*COBOL) is a combination of host indicator variable and NULL condition checking. For example:

isnull pic s9(4) comp value -1.

select
  'I found it'
from
  tableA
where
  ( field1 = :v1 or ( field1 is null and :v1:isnull is null ) ) and
  ( field2 = :v2 or ( field2 is null and :v2:isnull is null ) ) and
  ( field3 = :v3 or ( field3 is null and :v3:isnull is null ) )

This solution does in fact work, but it seems rather clumsy to me, and honestly I'm concerned with optimizing the performance on this query. Especially if my only existing index is: field1, field2, field3 (and there are a few million records in the table).

Does anyone know of a better/cleaner/more efficient way to do this? I imagine there has to be one - and I'd be extremely grateful to see one. Received on Thu Mar 03 2005 - 17:22:06 CET