CODEWITHSUNDEEP
sqldb2embeddeddb2-400rpgle
LppEdd
LppEdd

Reputation: 21152

DB2 embedded SQL in RPGLE

Lets assume we have this query:

SELECT T1.*, T2.* INTO :DS1, :DS2 FROM FILE1 AS T1
LEFT JOIN FILE2 AS T2 ON T1.KEY = T2.KEY
FETCH FIRST 1 ROW ONLY

Everything comes up okay if both records are found. But what happens if FILE2 record is not present?

SQLCOD -305 THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE

And even if the record from FILE1 is found, both DS are empty! That's a problem.
A way to overcome this is to use COALESCE on every field, but what if I've got hundreds of them?!
Another way is to use two distinct queries. But that's ugly if I want a cursor.

Is there a better way?

Upvotes: 1

Views: 1711

Answers (1)

Charles
Charles

Reputation: 23823

If you have columns that could be NULL, then you have to pass in an integer variable to be used as a NULL indicator. Then if the value for the result column is null, SQL puts a -1 in the indicator variable.

With a LEFT JOIN every column from the right table could be NULL.

The manual has the following example:

EXEC SQL
   SELECT COUNT(*), AVG(SALARY)
   INTO :PLICNT, :PLISAL:INDNULL
   FROM CORPDATA.EMPLOYEE
   WHERE EDLEVEL < 18

Note that there's no comma between the null-capable PLISAL and INDNULL.

When dealing with Data structures, you can pass in an null indicator array. So you code should be something like:

// xx should be the number of columns in T2
dcl-s indArr  int(5) dim(xx);

exec sql
  SELECT T1.*, T2.* INTO :DS1, :DS2 :indArr
  FROM FILE1 AS T1
    LEFT JOIN FILE2 AS T2 ON T1.KEY = T2.KEY
   FETCH FIRST 1 ROW ONLY;

As an FYI: it's considered a bad idea to use SELECT * in production code. You should an an explicit list of columns. That way somebody can come along later and add a column to your table without breaking your code.

Upvotes: 3

Related Questions