NULL problem

[sujosh]

In my SP I am trying to get the value from a table and use it for another SQL. The problem is when there is no rows in the table what will the return value be for this SQL

"SELECT max(trans_seq_num) into ltrans_seq_num FROM pv_trans"

I thought it will be null. When I tried this code it does not work becasue the IF condition does not work as it supposed to. It is passing in a null.. Any ideas why?



Thanks
-- begin snippet
SELECT max(trans_seq_num) into ltrans_seq_num FROM pv_trans;
if (ltrans_seq_num = null) Then
ltrans_seq_num := 1;
else
ltrans_seq_num := ltrans_seq_num + 1;
end if;
--end snippet

 

[Felgar]

I'm pretty sure that replacing

if (ltrans_seq_num = null) Then

with

if (ltrans_seq_num IS null) Then

will work. Any boolean expression that has NULL as a paramater will return NULL, which then drops you down to the else because the result of the IF is not TRUE. The IS NULL statement should give you the desired result.

 

[stefanhei]

Hi.
You can also use the %FOUND-Attribute:

SELECT max(trans_seq_num) into ltrans_seq_num FROM pv_trans;
IF SQL%FOUND THEN ...

Stefan

 

[sujosh]

Thanks Felgar and stefanhei!! It works like a charm.

 

[raygg]

How about checking for rowid > 1?

 

[KALYANIDRV]

Hi Sujosh,
Please to find and use the following script. It will be useful to eleminate the if..then..else statement in your code and it is also to increment to 1.


SELECT nvl(max(trans_seq_num),1)+1 into ltrans_seq_num FROM pv_trans ;

please let me know on the same.

Please contact me at ravich_74@hotmail.com

Thanks,
Ravi.