At 09:42 AM 14-08-01 -0600, you wrote:

> > No, it shouldn't happen. We'd need to see the DDL involved to work out
>why this happens to your app.
> >
> > Corrupt indexes don't work. Your application could, through some kind of
>logic error, write a wrong value in a key, thus "corrupting" data integrity.
> >
> > More info please.
>
>Thanks Helen,
>Here is the code that is used in the stored proc..

[snip]

No answers, Gordon, only more questions...just before I retire to bed (2:20 a.m. and fading fast..)

OK, the first question to ask on this: Why do you have a block for

if (V_CustID IS NOT NULL ) then

?

This query will not return any row with a null CustID because it is joined on that. NULL=NULL does not evaluate to True, so no output row would ever occur with NULL in the CustID column of either table. You would potentially be able to get a null CustID (with nulls in all the righthand values) with a left outer join but it's not the case here.

If there is the potential for null CustIDs to exist in C, we must assume that CustID is not the primary key in either table. Therefore, are you certain that your query always produces a one-row result set?

How can you tell when you have got a wrong CustID back?

The result code is suspect because you initialised it "successful". Perhaps you would get more clues by initialising it "unknown" and setting it to 0 only when you have eliminated all possible error conditions. If it comes back "unknown" then you will find any exceptions you didn't cover in the logic.

All that aside, if there is the possibility of the query returning multiple rows (test it interactively) then your approach here won't handle it. You would need a
FOR..SELECT...DO query.

If it WAS intended to return a single output row, then those suspends shouldn't be there.

Regards,

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________