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 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.