DB2 embedded SQL in RPGLE

Question

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?

If either must be present, change your LEFT JOIN with INNER JOIN — Joe Taras
– Joe Taras, Commented Dec 16, 2016 at 10:52

Charles · Accepted Answer · 2016-12-16 14:10:29Z

3

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.

edited Dec 16, 2016 at 14:10

answered Dec 16, 2016 at 13:59

Charles

24.2k3 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LppEdd Over a year ago

That was it! Awesome as always! And you're right about using single columns, but with 80 chars limitation, hundreds of fields become a bit tedious!

Charles Over a year ago

@LppEdd, it's not that difficult to generate a comma separated list of column names. If nothing else, you can get a list or columns into a text editor and replace the CRLF with comma.

Collectives™ on Stack Overflow

DB2 embedded SQL in RPGLE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related