Re: SQL IF

On Tue, 13 Jun 2000 11:22:28 -0400, Jack Ostroff <jack_h_ostroff_at_groton.pfizer.com> wrote:

>Greg wrote:
>>
>> If I have the SQL code
>>
>> if (variable1 <> variable2) then....
>>
>> Works if the Variables have a value
>> but if one of them is null, it doesn't.
>>
>> how to include the case one of the variables are null in the statement.
>
>(hopefully this newsreader will let me post...)
>
>Other readers have pointed out that NULL fails both = and <> tests.
>You might try DECODE(variable1, variable2, "they match", "they are
>different");
>I believe that this does the right thing with NULL values.
DECODE can only be used in the context of a SQL statement, not whithin an IF. So in order to use it one must do something like this:

SELECT DECODE (variable1,variable2,"they match", "they are different") INTO myVar
FROM dual;

IF (myVar = "they match")
THEN
......
ENDIF; Received on Thu Jun 15 2000 - 00:00:00 CEST