Re: Variable has NULL in IF-statement , but does insert a valid value

Roy Coumans wrote:

> Hi,
>
> I have a problem with a trigger in Oracle 8i.
> The var 'StatusKey' is the problem. In the first UPDATE statement I
> set the LAST_STATUS_KEY to a value from a variable : 'StatusKey'. This
> variable I select in the first SELECT-Statement. Then I update table
> TRANSACTIONS with this variable. This works fine, it updates table
> TRANSACTIONS as expected. So, you might think that var StatusKey has a
> value. However, it never passes the IF-statement at the bottom. If I
> write down 'IF StatusKey IS NULL' it does go into the IF-statement.
> So, that means that the var StatusKey is NULL. How can that be since
> it correctly updates the table TRANSACTIONS with this variable?
>
> Somebody has a logical answer on this un-logical issue? Thanks, Roy
>
> CREATE OR REPLACE TRIGGER DELETE_CUSTOMER_LOG
> AFTER DELETE ON STATUSDATE
> FOR EACH ROW
> DECLARE StatusKey VARCHAR2(5);
> ThisTaKey VARCHAR2(5);
> BEGIN
> SELECT MAX(TO_NUMBER(:NEW.SD_TA_STATUS_KEY)) INTO StatusKey
> FROM SYS.DUAL;
>
> SELECT :NEW.SD_TA_KEY INTO ThisTaKey
> FROM SYS.DUAL;
>
> UPDATE TRANSACTIONS
> SET LAST_STATUS_KEY = StatusKey
> WHERE TA_KEY = ThisTaKey;
>
> IF (StatusKey = '1' ) THEN
> DELETE FROM CUSTOMER_LOG
> WHERE LOG_TA_KEY = ThisTaKey; */
> END IF;
> END;
The value of StatusKey is defined in your DECLARE section as VARCHAR2.

Then you SELECT (MAX(TO_NUMBER( into it.

Which part of MAX(TO_NUMBER( do you think is going to produce a string?

Try turning your variable into a NUMBER and see if the problem goes away. Even if it doesn't it will be a positive step. If that doesn't solve the problem let us know.

Daniel Morgan Received on Mon Jul 22 2002 - 17:37:38 CEST