Pro*COBOL and (possible) NULL host variabe in WHERE condition
Date: 3 Mar 2005 07:56:59 -0800
Message-ID: <1109865419.462518.13810_at_g14g2000cwa.googlegroups.com>
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,