Skip to Content

How can I restrict the SELECTED data based on a internal table?

Hello everyone. I googled around a little, but couldn't really find what I am looking for, so here I am..

I have two Databank Tables, let's call them 'db1' and 'db2'. They both have the same field called 'f1'.

I also have a Select-Options (which I'm gonna call 'so') for the field db1-f2.

I want to select data from db1 'WHERE f2 IN so' but I want to exclude all the rows where the value of 'f1' exists in the table db2.

I thought I could do it like this:

TABLES: db1, db2.

DATA: lt1 TYPE TABLE OF db1,

      lt2 TYPE TABLE OF db2.

SELECT * FROM db2  INTO CORRESPONDING FIELDS OF TABLE lt2.

SELECT * FROM db1
  INTO CORRESPONDING FIELDS OF TABLE lt1
  FOR ALL ENTRIES IN lt2
  WHERE f1 <> lt2-f1
    AND f2 IN so.

Sadly this isn't working correctly.

Any ideas what I'm doing wrong?

Is there, maybe, a trick I can use with some sort of reverse JOIN?

Thank you for your time :)

Greetings from Vienna,

Nick

Add comment
10|10000 characters needed characters exceeded

  • What is the TABLES statement for?

    a) it is only needed for classical dynpros, b) you don't address the work areas in your code.

    I think you can remove it ...

  • Get RSS Feed

4 Answers

  • Best Answer
    Nov 16, 2017 at 02:49 PM

    Something like

    select a~f1, a~f2 from ( db1 as a 
             left outer join db2 as b
                          on a~f1 = b~f1 ) 
        where a~f2 in @so 
          and b~f1 = space.
    Add comment
    10|10000 characters needed characters exceeded

    • Hello. Thank you very much for your help. It worked :)

      Just for reference here is how the code looks now:

      DATA: BEGIN OF lty_fieldname,
                fieldname(10),
              END OF lty_fieldname .
      
      DATA: lt_fieldname LIKE TABLE OF lty_fieldname,
            ls_fieldname LIKE LINE OF lt_fieldname.
      
      DATA: gt_kna1 TYPE TABLE OF kna1.
      
      * I'm using the lt_fieldname only because I need dynamic field selection
      ls_fieldname-fieldname = 'K~KUNNR,'.  " <-- Note the necessary comma here
      APPEND ls_fieldname TO lt_fieldname.
      
      ls_fieldname-fieldname = 'K~NAME1'.
      APPEND ls_fieldname TO lt_fieldname.
      
      SELECT (lt_fieldname)
          FROM (  kna1 AS k
                  LEFT OUTER JOIN cvi_cust_link AS c
                  ON k~kunnr = c~customer )
          INTO CORRESPONDING FIELDS OF TABLE @gt_kna1
          WHERE k~kunnr IN @so_deb
            AND c~partner_guid IS NULL.
      
  • Nov 16, 2017 at 03:21 PM

    You could try a subquery like

    SELECT * FROM db1
      INTO [CORRESPONDING FIELDS OF] TABLE lt1
      WHERE f2 IN so
        AND NOT EXISTS( SELECT * FROM db2 WHERE f1 EQ db1~f1 ).
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 16, 2017 at 04:48 PM

    For all entries will not work in case of 'Not Equal To'.It will not give correct result.

    You can do like below:

    Do not use Corresponding if fields fetch and target structure are same(just a suggestion).

    Please find below a suggestion, let me know if you want full code

    SELECT *FROM db2 INTO CORRESPONDING FIELDS OF TABLE lt2.
    SELECT *FROM db1
      INTO CORRESPONDING FIELDS OF TABLE lt1
      FOR ALL ENTRIES IN lt2
      WHERE f2 IN so.
    
    loop at lt1
     read table lt2 comparing f1 = lt1-f1
      Delete the entries from lt1 if found..
    endloop.
    Add comment
    10|10000 characters needed characters exceeded

  • Nov 17, 2017 at 04:38 AM

    Use antijoin:

    SELECT db1~*
      FROM db1 LEFT JOIN db2 ON db1~f1 = db2~f1
      WHERE db1~f2 IN so
        AND db2~f1 IS NULL.
    Add comment
    10|10000 characters needed characters exceeded