topic Re: How to construct such SELECT in Application Development and Automation Discussions

How to construct such SELECT

Former Member — Thu, 29 Mar 2007 11:14:52 GMT

I simplify my problem as much as it is possible.

I have two tables TABLE1 and TABLE2. In both table key look:

key1 key2.

I have also an internal table INT_TABLE2 which is of type of TABLE2 which have some entries.

I want to select from TABLE1 all not existing pairs (TABLE1.key1 = TABLE2.key1 AND TABLE1.key2 = TABLE2.key2) for all entries in INT_TABLE2.

I tried


     SELECT T2~key1
       INTO CORRESPONDING FIELDS OF TABLE et_not_pairs
       FOR ALL ENTRIES IN INT_TABLE2
       FROM ( TABLE1 AS T1 OUTER JOIN TABLE2 AS T2
                ON T1~KEY1  = T2~KEY1 AND
                      T1~KEY2 =  T2~KEY2 )
           WHERE T2~KEY1 = INT_TALBE2-KEY1 AND
                        T2~KEY2 = INT_TALBE2-KEY2.

but without effect (error)

Re: How to construct such SELECT

Former Member — Thu, 29 Mar 2007 11:25:35 GMT

Hi!

Always check, is the internal table empty or not, because with an empty internal table, SAP will read ALL entries from T1 and T2, and this will cause bad performance, and/or abap dump (due to the long runtime).


  IF NOT INT_TABLE2 IS INITIAL.
     SELECT T2~key1
       INTO CORRESPONDING FIELDS OF TABLE et_not_pairs
       FROM  TABLE1 AS T1 OUTER JOIN TABLE2 AS T2
                ON T1~KEY1  = T2~KEY1 AND
                   T1~KEY2 =  T2~KEY2 
       FOR ALL ENTRIES IN INT_TABLE2
        WHERE T2~KEY1 = INT_TABLE2-KEY1 AND
              T2~KEY2 = INT_TABLE2-KEY2.
  ENDIF.

Regards

Tamá

Re: How to construct such SELECT

Former Member — Thu, 29 Mar 2007 12:25:29 GMT

I know I check it is empty, but I didn't paste this checking here

Re: How to construct such SELECT

Former Member — Thu, 29 Mar 2007 15:02:02 GMT

Hello

Try with this code.

SELECT T2~key1

INTO CORRESPONDING FIELDS OF TABLE et_not_pairs

FROM ( TABLE1 AS T1 INNER JOIN TABLE2 AS T2

ON T1_{KEY1 = T2}KEY1 AND

T1_{KEY2 = T2}KEY2 ).

Regards

shibino sooryan.k

Re: How to construct such SELECT

Former Member — Fri, 30 Mar 2007 07:49:12 GMT

But INNER JOIN return existing pairs, I need not existing pairs

Re: How to construct such SELECT

Former Member — Tue, 03 Apr 2007 14:36:02 GMT

Found solution myself. I replaced OUTER JOIN with two selects