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

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.com> wrote in message news:1109866926.730294.310650_at_f14g2000cwb.googlegroups.com...
> 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 - 18:43:10 CET