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

Chris wrote:

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.

Make sure your table is not defaulting to NULLS, and insert spaces is one
option - no benefit, and defeats purpose of varchar. So no value.

I didn't know you could index on a varchar - this seems strange to me as for
most sorting/indexing algorithms [that I know of and I'm not familliar with
Oracle] the data would be expanded to the full size internally and at 10
characters you are only saving 8 bytes a record...hardly worth it in the
first place.

The host isnull variables are more important on the select. A lot of shops
will check that null indicator =-1 to determine if the select returns a null
which is not actually right as the value is set on the insert statement (and
defaulted if not). You just need to make sure that you set the null
indicator for each of the fields prior to calling the select - this is not a
big deal.

If you are worried about the performance have the DBA run some kind of
explain on it and check the execution path etc. Like I said, I've never
seen a VARCHAR as a key field before.

The only other option is to dynamically build the SQL - depending on how you
do this the performance drop off is negligible as the statements are access
plan are cached. So you could build the where clause. I would have done
it the way you did most likely - but I wouldn't have varchars.

So, I don't see anything wrong with this at all. You have a full key select
statement...should fly through this if your keys have a high cardinality.

JCE


"Chris" <ctaliercio (AT) yahoo (DOT) com> wrote