How to use SQL coalesce with a null whole fetched row

The case I am trying to solve is this: for every row in a table another row from a second table might exist, so I need all data from the row of the first table and the data from the row of the second table if present.

I know I can use data structures as host variables to gather all data from a row in a table. So, my select is this:

select
    t1.*
   ,t2.*
into
    :dst1
   ,:dst2
from table1 t1
  left join table2 t2 on t2.key=t1.key
;

where dst1 and dst2 are data structures respectively like table1 and table2 records' format. Pretty simple.

Now, the point is how to catch null result when a row for that key doesn't exist in the second table. In that case I would like to have the corresponding data structure initialized, but coalesce works on one field at a time and I haven't been able to find another solution.

Is there a way to obtain this result?

Any help would be appreciated!

Thanks

Solution

One way to deal with this is to use indicator variables. It looks like this:

dcl-ds hs    Qualified;
  field1 ...
  field2 ...
endds;

dcl-s hsind  Int(5) Dim(2);

exec sql
  select *
    into :hs:hsind
    from table
    fetch first row only;

Note, there is no comma (,) between :hs and :hsind as this is part of the same variable assignment. :hsind is an indicator variable, and in this case is an array of Int(5) with the same number of elements as the host data structure :hs has fields. The indicator variable will contain a 0 if the value in the associated field in :hs is good, or -1 if it is null. So in our example above: If hs.field1 is good, and hs.field2 is null, then hsind(1) = 0, and hsind(1) = -1. Other values mean other things like data mapping error (-2), or string truncation (positive number with original length of string).

So in your example, use something like this:

select
    t1.*
   ,t2.*
into
    :dst1:dst1ind
   ,:dst2:dst2ind
from table1 t1
  left join table2 t2 on t2.key=t1.key
;

Where dst1ind is an array if Int(5) with the same number of elements as dst1 has subfields, similarly for dst2ind. Then after your selection, just check dst2ind(1) >= 0, and you have a good select. Notice that you will need to make sure that select into only returns a single row, or you will get errors about that.