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

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 Received on Thu Mar 03 2005 - 09:56:59 CST