Two OPEN SQl statements with same WHERE condition, different Columns but returning different numbers

[Above]

Hi Experts,

I need your help with the OPEN SQL statements shared below. I'm comparing two SQL statements that use the same WHERE condition but have different numbers of columns. I expect both statements to return the same number of records since the WHERE condition is the same. However, when I execute the SQL statements, I find that the number of records returned is different. Can someone explain why this is happening?

SELECT plpo~plnty,
           plpo~plnnr,
           plpo~plnkn,
           plpo~zaehl,
           plpo~vgw01,
           plpo~vgw02,
           plpo~vgw03,
           plpo~bmsch,
           plpo~vornr,
           plpo~vge01,
           plpo~vge02,
           plpo~vge03,
           plpo~steus,
           plas~loekz,
           plas~plnfl,
           plas~plnal,
           crhd~arbpl,
           crhd~objid
       FROM plpo
          INNER JOIN plas ON plpo~plnty = plas~plnty
           AND plpo~plnnr = plas~plnnr
           AND plpo~plnkn = plas~plnkn
           AND plpo~zaehl = plas~zaehl
         INNER JOIN crhd ON plpo~arbid = crhd~objid
      FOR ALL ENTRIES IN @t_operazioni
         WHERE plpo~plnty = @t_operazioni-plnty
           AND plpo~plnnr = @t_operazioni-plnnr
           AND plas~plnal = @t_operazioni-plnal
           AND plpo~steus <> 'ZP00'
           AND plas~loekz IS INITIAL
           AND plas~plnfl = '000000'
      INTO TABLE @DATA(t_plpo).


    SELECT
           plpo~vgw01,
           plpo~vgw02,
           plpo~vgw03,
           plpo~bmsch,
           plpo~vornr,
           plpo~vge01,
           plpo~vge02,
           plpo~vge03,
           crhd~arbpl

            FROM plpo
           INNER JOIN plas ON plpo~plnty = plas~plnty
                         AND plpo~plnnr = plas~plnnr
                         AND plpo~plnkn = plas~plnkn
                         AND plpo~zaehl = plas~zaehl
          INNER  JOIN crhd ON plpo~arbid = crhd~objid
      FOR ALL ENTRIES IN @t_operazioni
            WHERE plpo~plnty = @t_operazioni-plnty AND
                plpo~plnnr = @t_operazioni-plnnr AND
                plas~plnal = @t_operazioni-plnal AND
                plpo~steus <> 'ZP00' AND
                plas~loekz IS INITIAL AND
                plas~plnfl = '000000'
    INTO TABLE @DATA(t_plpo2).

[BaerbelWinkler · Accepted solution]

@Above 

Hi!

You have a "FOR ALL ENTRIES" construct in your SELECT statement and this has the effect that it only gives DISTINCT values back in the results. I don't know the tables involved in your statement but you may need to add whatever the key fields for PLPO are in the field list of your 2nd select to ensure that you get all the expected entries (even if you don't necessarily need the fields in the subsequent logic).

Hope this helps!

[UNOX · Accepted solution]

.

[Above · Accepted solution]

Perfect! Thank you so much for your help!

[BaerbelWinkler · Accepted solution]

If it worked, please provide the solution in a comment and then also accept my answer as solution. Thanks!

[Above · Accepted solution]

Hi @BearbelWinkler, As per your abservation, in the second SELECT statement's field list, I included the key fields from the PLPO table: plpo~plnty, plpo~plnnr, and plpo~plnkn. This ensured that I received the same output. Thank you very much for sharing your knowledge!