CODEWITHSUNDEEP
sqldb2
Gobbledigook
Gobbledigook

Reputation: 472

DB2 set default when null on join / Open table after FETCH

This is kind of a double question, just thinking of ways to accomplish my problem. Also, I'm pretty new to DB2 and stored procedures, so bear with me.

I'm creating a stored procedure that gets a value from two tables using a Left Join statement. This will result in some of the values in the second table returning a null value (since they don't exist in tableB). 

DECLARE CURSOR C1 WITH RETURN FOR 
select a.name, a.title, b.order from tableA a
left outer join tableB b on a.name = b.name;

Now, I need some way to set these null values to a default value of 0.

The program I'm working with can do it ( CAST-IRON ) but if the result set is too large, it slows down the orchestrations and truncates the job log. So I'm trying to figure it out using the stored procedure.

My first thought was to use the FETCH INTO statement and a WHILE loop. 

WHILE AT_END = 0 DO
FETCH C1 INTO CHNAME, CHTITLE, CHORDER; 
IF CHORDER IS NULL 
THEN SET CHORDER = 0;
END IF;
IF SQLCODE = 100 
THEN SET AT_END = 1;
END IF;
END WHILE;

But it seems like that would require a temporary table being created, and declaring another cursor with that table, using an insert command after the 'FETCH INTO'. So I was wondering if there were another way to do this, or to automatically set a default in the select statement?

Upvotes: 1

Views: 3068

Answers (1)

Ben
Ben

Reputation: 35613

Set a default in the select statement using COALESCE.

DECLARE CURSOR C1 WITH RETURN FOR  
select a.name, a.title, COALESCE(b.order,0) as order
from tableA a 
left outer join tableB b on a.name = b.name;

Upvotes: 4

Related Questions