topic Select join in Application Development and Automation Discussions

Select join

former_member687484 — Thu, 25 Jun 2020 11:49:41 GMT

The data selected for a given vendor is compared to the data selected for the other vendors.

The following fields are compared:

Name: LFA1- NAME1

Address ID: LFA1 – ADRNR

Street: LFA1 – STRAS

City: LFA1 - ORT01

VAT reg number: LFA1 – STCEG

Phone number: ADR2 - TEL_NUMBER

Bank account: LFBK – BANKN

If any field matches to another vendor in the selected data, the vendor is added to the output dataset.

how can i, select and join the Records when they are Same, From Table (lfba1, lfb1,adr2 and lfbk) to a new table? in ABAP

Re: Select join

MateuszAdamus — Thu, 25 Jun 2020 12:09:21 GMT

Hello adib.1213

Something like the below code. Not 100% sure if it will work, wrote it in the forum's editor. Also, not sure about the performance.

SELECT lfa1~lifnr
  FROM lfa1
  JOIN adr2
    ON adr2~addrnumber = lfa1~adrnr
  JOIN lfbk
    ON lfbk~lifnr = lfa1~lifnr
  LEFT OUTER JOIN lfa1 AS lfa1_copy
    ON ( lfa1_copy~name1 = lfa1~name1 OR lfa1_copy~adrnr = lfa1~adrnr OR /and other fields from LFA1/ )
    AND lfa1_copy~lifnr <> lfa1~lifnr
  LEFT OUTER JOIN adr2 AS adr2_copy
    ON adr2_copy~tel_number = adr2~tel_number
    AND adr2_copy~addrnumber <> adr2~adrnr
  LEFT OUTER JOIN lfbk AS lfbk_copy
    ON lfbk_copy~bankn = lfbk~bankn
    AND lfbk_copy~lifnr <> lfbk~lifnr.
  WHERE lfa1_copy~lifnr IS NOT NULL 
     OR adr2_copy~adrnr IS NOT NULL
     OR lfbk_copy~lifnr IS NOT NULL.

But this query, even if it works (which I'm not 100% sure), has one significant issue - only vendors which have records in all tables (LFA1, ADR2, LFBK) are compared.

If you'd like to compare these tables separately, then you'd probably need to write separate queries.

Kind regards,

Mateusz

Re: Select join

former_member1954 — Thu, 25 Jun 2020 13:32:55 GMT

It depends of your needs.

You can also create simple SQL select for one field. i.e Name.

And next use UNION to select vendors for another field i.e Street.

and so on.

UNION returns only unique rows. So no duplicates at the end.

EXISTS might be also an option in some scenarios.

best regards

Rafal

Re: Select join

former_member687484 — Thu, 25 Jun 2020 14:55:59 GMT

thanks for Replaying me,

for first steps, for the first part of my task i write this code and its work.

**TABLES : LFA1, LFB1, LFBK, ADR2.

SELECTION-SCREEN BEGIN OF BLOCK B1 with frame title text-001.

select-OPTIONS : S_kTOKK for LFA1-KTOKK,
S_BUKRS for LFB1-BUKRS.

SELECTION-SCREEN end of BLOCK B1.

*SELECT LFA1~*

Select
LFA1~LIFNR,
LFA1~NAME1,
LFB1~BUKRS,
LFA1~ADRNR,
LFA1~STRAS,
LFA1~ORT01,
LFA1~PSTLZ,
LFA1~STCEG,
LFB1~ERDAT,
LFBK~BANKN,
ADR2~TEL_NUMBER,
LFA1~SPERR,
LFB1~SPERR as SPERRB

FROM LFA1 INNER JOIN LFB1 on LFA1~LIFNR eq LFB1~LIFNR
INNER JOIN LFBK on LFBK~LIFNR eq LFA1~LIFNR
LEFT JOIN ADR2 on LFA1~ADRNR eq ADR2~ADDRNUMBER
WHERE LFA1~KTOKK in @S_KTOKK and LFB1~BUKRS in @S_BUKRS
INTO TABLE @data(Lt_Vendors).**

for the second step to select and join the fields with same values from this 4 table i need to a new selection.?

logically i need an output table (X), that Select and join are those/this fields from this tables ( FLA1,FLB1,ADR2 and LFBK) that they have a same values in this fields Like

(

DATA Source

LFA1-LIFNR LFA1-NAME1 LFB1-BUKRS LFA1-ADRNR LFA1-STRAS LFA1-ORT01 LFA1-PSTLZ ADR2-TEL_NUMBER LFA1-STCEG LFBK-BANKN LFB1-ERDAT LFA1-SPERR LFB1-SPERR

)

and a output like this

The following columns are used in the output list:

Column Data Source Vendor ID LFA1-LIFNR Vendor Name LFA1-NAME1 Company Code LFB1-BUKRS Address Number LFA1-ADRNR Street LFA1-STRAS City LFA1-ORT01 Postcode LFA1-PSTLZ Phone ADR2-TEL_NUMBER VAT Number LFA1-STCEG Bank Account LFBK-BANKN Create Date LFB1-ERDAT Central Posting Block LFA1-SPERR Company Posting Block LFB1-SPERR

Re: Select join

MateuszAdamus — Thu, 25 Jun 2020 15:17:31 GMT

Hello adib.1213

Yes, if I understand your requirement correctly, and looking at what you've already written, you'd need at least one more SELECT query to get the vendors with the same data. However, most of these fields that you want to check don't look like fields that are indexed. This means that a DB query based on these fields (with OR condition for some of these tables) might be quite slow (depends on the DB engine you have, might be a different story if you run on HANA).

If you select only those fields you listed, then I wonder if it's not a better idea to load all the records into internal table(s) and compare the fields' values in memory.

Kind regards,

Mateusz

Re: Select join

former_member687484 — Thu, 25 Jun 2020 15:21:41 GMT

thanks for Replaying me,
*for first steps, for the first part of my task i write this code and its work.
TABLES : LFA1, LFB1, LFBK, ADR2.

SELECTION-SCREEN BEGIN OF BLOCK B1 with frame title text-001.

select-OPTIONS : S_kTOKK for LFA1-KTOKK,
S_BUKRS for LFB1-BUKRS.

SELECTION-SCREEN end of BLOCK B1.

*SELECT LFA1~*

Select
LFA1~LIFNR,
LFA1~NAME1,
LFB1~BUKRS,
LFA1~ADRNR,
LFA1~STRAS,
LFA1~ORT01,
LFA1~PSTLZ,
LFA1~STCEG,
LFB1~ERDAT,
LFBK~BANKN,
ADR2~TEL_NUMBER,
LFA1~SPERR,
LFB1~SPERR as SPERRB


FROM LFA1 INNER JOIN LFB1 on LFA1~LIFNR eq LFB1~LIFNR
INNER JOIN LFBK on LFBK~LIFNR eq LFA1~LIFNR
LEFT JOIN ADR2 on LFA1~ADRNR eq ADR2~ADDRNUMBER
WHERE LFA1~KTOKK in @S_KTOKK and LFB1~BUKRS in @S_BUKRS
INTO TABLE @data(Lt_Vendors).
**for the second step to select and join the fields with same values from this 4 table i need t**o a new selection.?
**logically i need an output table (X), that Select and join are those/this fields from this ta**bles ( FLA1,FLB1,ADR2 and LFBK) that they have a same values in this fields Like
**(
DATA Source
LFA1-LIFNR 
LFA1-NAME1 
LFB1-BUKRS 
LFA1-ADRNR 
LFA1-STRAS 
LFA1-ORT01 
LFA1-PSTLZ 
ADR2-TEL_NUMBER 
LFA1-STCEG 
LFBK-BANKN 
LFB1-ERDAT 
LFA1-SPERR 
LFB1-SPERR
).**

**and a output like this
**The following columns are used in the output list:
*Column         Data Source: **
*(  ↓            ↓
Vendor ID      LFA1-LIFNR 
VendorName     LFA1-NAME1 
CompanyCode    LFB1-BUKRS 
Addres Number  LFA1-ADRNR 
Street         LFA1-STRAS 
City           LFA1-ORT01 
Postcode       LFA1-PSTLZ 
Phone          ADR2-TEL_NUMBER 
VAT Number     LFA1-STCEG 
Bank Account   LFBK-BANKN 
Create Date    LFB1-ERDAT 
Central Posting Block LFA1-SPERR 
Company Posting Block LFB1-SPERR
)