openedge - Does a OUTER-JOIN always divide the query in two parts, leaving the part on the right empty if not complete in Progress?

Question

I'm trying to do an OUTER-JOIN in progress using this page as inspiration. My code is as follows

OPEN QUERY qMovto
FOR EACH movto-estoq
WHERE movto-estoq.lote BEGINS pc-lote
AND movto-estoq.it-codigo BEGINS pc-it-codigo
AND movto-estoq.dt-trans >=  pd-data1
AND movto-estoq.dt-trans <=  pd-data2
AND movto-estoq.cod-emitente = pi-cod-emitente,
    EACH item OUTER-JOIN
    WHERE movto-estoq.it-codigo = item.it-codigo,
        EACH item-cli OUTER-JOIN
        WHERE item-cli.item-do-cli BEGINS pc-item-cli
        AND movto-estoq.cod-emitente = item-cli.cod-emitente
        AND movto-estoq.it-codigo = item-cli.it-codigo
        AND movto-estoq.un = item-cli.unid-med-cli,    
            EACH nota-fiscal OUTER-JOIN
            WHERE movto-estoq.nro-docto = nota-fiscal.nr-nota-fis
            BY movto-estoq.dt-trans DESCENDING BY movto-estoq.hr-trans DESCENDING.

The problem that is happening is when 1 element in null, all the other elements that are in the OUTER-JOIN are appearing as null as well, even though they are not null. Is there a better way to write this code? Should I put 'LEFT' before the OUTER-JOIN? Thanks for your time.

question from:https://stackoverflow.com/questions/65890635/does-a-outer-join-always-divide-the-query-in-two-parts-leaving-the-part-on-the

Answer 1

To make your example easier, consider making it work from ABL dojo. The following code:

define temp-table ttone
   field ii as int
   .

define temp-table tttwo
   field ii as int
   field cc as char
   .

create ttone. ttone.ii = 1.
create ttone. ttone.ii = 2.
create tttwo. tttwo.ii = 2. tttwo.cc = "inner".
create tttwo. tttwo.ii = 3. tttwo.cc = "orphan".

define query q for ttone, tttwo.
open query q 
    for each ttone, 
    each tttwo outer-join where tttwo.ii = ttone.ii.
    
get first q.
do while available ttone:
   message ttone.ii tttwo.cc.
   get next q.
end.

Can be run from https://abldojo.services.progress.com/?shareId=600f40919585066c219797ed

As you can see, this results in :

1 ?
2 inner

The join which is not available is shown as unknown. The value of the outer part of the join is shown.

Since you do not show how you are getting an unknown value for everything, maybe you are concatenating the unknown values?