Weird SQL behavior (complex JOIN, VBAK+VBPA)

[VXLozano]

I have a "complex" SQL. Something like this:

    select vbak~vbeln as numpedido, vbak~auart as tipo, vbak~erdat as creado, vbak~ernam as creadopor,
           sum( pmco~wrt01 + pmco~wrt02 + pmco~wrt03 + pmco~wrt04 + pmco~wrt05 + pmco~wrt06 + pmco~wrt07
              + pmco~wrt08 + pmco~wrt09 + pmco~wrt10 + pmco~wrt11 + pmco~wrt12 ) as costes,
           vbak~waerk as moneda, pa0001~ename as vendedor, but000~mc_name1 as cliente,
           vbak~uvfak as statusfac
      from vbak
                left join vbpa on vbak~vbeln = vbpa~vbeln
                left join caufv on vbak~vbeln = caufv~kdauf
                left join pmco on caufv~objnr = pmco~objnr
                left join pa0001 on vbpa~pernr = pa0001~pernr
                left join but000 on vbak~kunnr = but000~partner
      where vbak~vbeln in @rpedidos
        and vbak~auart in @rangotipos
        and vbak~vkbur in @roficinas
        and vbak~erdat between @sqldesde and @sqlhasta
        and vbak~vkbur in @roficinas
        and caufv~autyp = '30'
        and pmco~wrttp = '04'
        and pmco~acpos <> 'ZDESP'
        and vbpa~parvw = 'Z1' "vendedor
        and vbpa~pernr in @rangovendedores
      group by vbak~vbeln, vbak~auart, vbak~erdat, vbak~ernam, vbak~waerk, pa0001~ename,
               but000~mc_name1,
               vbak~uvfak
      into corresponding fields of table @pedidos
      bypassing buffer.

(the "bypassing buffer" was my last chance option)

I have some documents that do NOT appear in the result. All of a concrete type (AUART). I checked the tables, and they have rows that fulfill the JOIN (even if they don't, those docs are in VBAK, and therefore, they should appear in the query result).

What did I do?

I left just the vbak~vbeln begun to remove the JOIN tables one by one until those docs appeared. The guilty table was VBPA.For some reason, I decided to add the VBPA table and try again, and it worked*

"Nice", I thought, I quit the program and executed it again from scratch. BOOOO!! No more docs.

This behavior repeats each time, and it's independent of the order of the tables removed. Once I remove the VBAP, the query works each time, even when I add the VBAP table. Then I execute a second time, and the docs are missing again.

Any idea?

While I wait for some kind help, I will try to search again, it occurs (zero) to me that maybe one of those tables is one of the "new" ones (I'm in S/4 Hana) and it doesn't like JOINs.

Thanks in advance, even if you just had the patience to read 'til the end.

Edit: SE11 says those tables are "normal" ones, so no cookies for me.

* (it's because that that I added the bypassing buffer thing).

[VXLozano]

I'm an idiot (and not just a tuly one) and my key users forget to tell me some "non-critical" information. The missing documents have a workflow that makes them to NOT fill the CAUFV-KDAUF field, but the VBAK-AUFNR one.

So, the JOIN will return no data for those documents.

The weird thing is SAP ignoring the OUTER part of the join. It's supposed "left outer" gives you a row for the left side of the join even if there's no info in the right one.

Or am I wrong?

[Sandra_Rossi]

You need to move your table selections from the WHERE to the ON. It's required because your joins are OUTER joins, otherwise that won't work if at least one table has no record (only IS NULL would be a successful condition).

PS: remember that functionally speaking, the WHERE is applied after the selection is done.

[VXLozano]

Didn't knew about the ON thing. I'll give a try. Thank you very much, sandra.rossi

[VXLozano]

Can you please convert your comment to an answer? So I can mark this idiotic question as answered (because it solves my problem) and prevent other people to come here to waste their time.
Thank you again.

[VXLozano]

It didn't work... I've got a flu, and I cannot think clearly. I am splitting the JOINs in SELECTs and will LOOP them.

[Sandra_Rossi]

You need to move your table selections from the WHERE to the ON. It's required because your joins are OUTER joins, otherwise that won't work if at least one table has no record (only IS NULL would be a successful condition).

PS: remember that functionally speaking, the WHERE is applied after the selection is done.

    select vbak~vbeln as numpedido, vbak~auart as tipo, vbak~erdat as creado, vbak~ernam as creadopor,
           sum( pmco~wrt01 + pmco~wrt02 + pmco~wrt03 + pmco~wrt04 + pmco~wrt05 + pmco~wrt06 + pmco~wrt07
              + pmco~wrt08 + pmco~wrt09 + pmco~wrt10 + pmco~wrt11 + pmco~wrt12 ) as costes,
           vbak~waerk as moneda, pa0001~ename as vendedor, but000~mc_name1 as cliente,
           vbak~uvfak as statusfac
      from vbak
                left join vbpa on vbak~vbeln = vbpa~vbeln
                              and vbpa~parvw = 'Z1' "vendedor
                              and vbpa~pernr in @rangovendedores
                left join caufv on vbak~vbeln = caufv~kdauf
                              and caufv~autyp = '30'
                left join pmco on caufv~objnr = pmco~objnr
                              and pmco~wrttp = '04'
                              and pmco~acpos <> 'ZDESP'
                left join pa0001 on vbpa~pernr = pa0001~pernr
                left join but000 on vbak~kunnr = but000~partner
      where vbak~vbeln in @rpedidos
        and vbak~auart in @rangotipos
        and vbak~vkbur in @roficinas
        and vbak~erdat between @sqldesde and @sqlhasta
        and vbak~vkbur in @roficinas
      group by vbak~vbeln, vbak~auart, vbak~erdat, vbak~ernam, vbak~waerk, pa0001~ename,
               but000~mc_name1, vbak~uvfak
      into corresponding fields of table @pedidos.