topic Re: Intersection in Select statement in Application Development and Automation Discussions

Intersection in Select statement

Former Member — Tue, 05 Oct 2010 14:31:38 GMT

Hi,

Do any one know about some intersection option in select statement.

My case is : I have 2 database tables say TAB1 and TAB2 . TAB1 has data (A, B, C, D) and TAB2 has data (B, D) now I want the output as (A and C) after joining these 2 tables and selecting.

That is I want the values of Table1 which are not there in Table2.

I have the option of selecting Table1 and then for all entries fetch Table2, then Loop at the Table & delete if record exists.

But this is creating performance issue as the number of records in both tables are in Lakhs.

So I wanted to know is there any option like Left Intersecion or some thing like left outer join and all, so that I can pick the dat in one select.

Thanks,

Sameer

Re: Intersection in Select statement

Former Member — Tue, 05 Oct 2010 14:33:11 GMT

LEFT OUTER JOIN.

Look it up in the ABAP help.

Re: Intersection in Select statement

ThomasZloch — Tue, 05 Oct 2010 14:36:46 GMT

You should be able to do this with a LEFT JOIN of these two tables (check F1 help). However if I remember correctly you cannot use fields of tab2 in the WHERE-condition, as in WHERE TAB2~FIELD1 IS NULL, to not even select those lines where tab2 has no matching entry.

You can delete these rows in a second step from the result table though.

Thomas

P.S. please use international units only, lakh = 100,000

Re: Intersection in Select statement

Former Member — Tue, 05 Oct 2010 15:02:38 GMT

I think a subquery may work here. Something like:

SELECT form TAB2 where not in TAB1.

Rob

Re: Intersection in Select statement

Former Member — Tue, 19 Oct 2010 06:37:07 GMT

Issue solved :

Sample Code

TYPES : BEGIN OF X_VBAK,

VBELN TYPE VBAK-VBELN,

AUBEL TYPE VBRP-AUBEL.

TYPES : END OF X_VBAK.

DATA : I_VBAK TYPE STANDARD TABLE OF X_VBAK.

DATA : I_VBAS TYPE STANDARD TABLE OF X_VBAK.

SELECT A_{VBELN B}AUBEL INTO CORRESPONDING FIELDS OF TABLE I_VBAK FROM ( VBAK AS A

LEFT OUTER JOIN VBRP AS B ON A_{VBELN EQ B}AUBEL ).

SORT I_VBAK BY VBELN.

delete ADJACENT DUPLICATES FROM i_vbak COMPARING ALL FIELDS.

DELETE I_VBAK WHERE AUBEL NE ''.

SORT I_VBAK BY VBELN.

SORT I_VBAS BY VBELN.