Muthu Annamalai schrieb:

> Need help in my Stored Procedure which is as follows
>
> SELECT COALESCE(OPENINGBALANCE,0), COALESCE(CASHIN,0),
> COALESCE(CASHOUT,0), COALESCE(CLOSINGBALANCE,0)
> FROM CASHACTIVITY
> WHERE CASHACTIVITY.TODAY =:SALESDAY
> INTO :OPENINGBALANCE,:CASHIN,:CASHOUT,:CLOSINGBALANCE;
>
> In CashActivity Table, if there is no record for given salesday then
> my SP is returning nulls, which I expect to return zero's.

No, you're mixing up NULLS with NOT FOUND.
If your stored proc doesn't find any matching row it will return the
initalized variables (which is NULL).

For example:

I = 1;
WHILE ( I <= 10 ) DO
BEGIN
RETURN_VALUE = 0; /* initializing the variable */
SELECT VALUE1
FROM SOMETABLE
WHERE VALUE2 = :I
INTO :RETURN_VALUE1
/* if this WHERE won't match any records, the RETURN_VALUE
variable will stay untouched, meaning it will hold the last
value it had in the loop, unless you initialize it (a), since
no record found doesn't return NULL, it returns simply nothing */
SUSPEND;
I = I + 1;
END


So change your SP to sth. like:

OPENINGBALANCE = 0;
CASHIN = 0;
CASHOUT = 0;
CLOSINGBALANCE = 0;

SELECT COALESCE(OPENINGBALANCE,0), COALESCE(CASHIN,0),
COALESCE(CASHOUT,0), COALESCE(CLOSINGBALANCE,0)
FROM CASHACTIVITY
WHERE CASHACTIVITY.TODAY =:SALESDAY
INTO :OPENINGBALANCE,:CASHIN,:CASHOUT,:CLOSINGBALANCE;

> I could easily handle this situation using if null construct, the
> problem is ( recently ) after learning about COALESCE i changed many
> of my SP's and now I am scared whether I am using coalesce in right
> sense.

Luc.