Skip to Content
0

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

Nov 16, 2017 at 02:22 PM

79

avatar image

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

10 |10000 characters needed characters left 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 ...

0
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Mike Pokraka Nov 16, 2017 at 02:49 PM
1

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.
Show 1 Share
10 |10000 characters needed characters left 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.
0
Raymond Giuseppi
Nov 16, 2017 at 03:21 PM
1

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 ).
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 16, 2017 at 04:48 PM
0

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.
Share
10 |10000 characters needed characters left characters exceeded
Evgeniy Astafev Nov 17, 2017 at 04:38 AM
0

Use antijoin:

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