ABAP Joins: Types, Syntax, and Examples for Efficient Data Retrieval
VBAK(Sales Document header Table)
VBELN -> Sales Document Header
AUART -> Sales Document Type
AUDAT-> Sales Document Date
KUNNR -> Customer Number
BUKRS -> Company Code
VBAP(Sales Document Item Table)
VBELN -> Sales Document Number
POSNR -> Item Number
MATNR-> Material Number
KWMENG -> Material Quantity
MEINS -> UOM
NETWR -> Net Value
KNB1 (Customers under company Table):
BUKRS : Company code
KUNNR : Customer number
AKONT : Recon Account.
LFB1 (Vendors under company Table)
BUKRS : Company code
LIFNR : Vendor number
AKONT : Recon Account
T001W(Plant description table):
WERKS : Plant number
NAME1 : Plant name
T001L (Storage location description Table):
WERKS : Plant number
LGORT : Storage location number
LGOBE : Storage location des.
Note : if we want to link any two tables, then the link fields must be primary fields in at least one database table.
Data base tables Key fields
T001 ——————> BUKRS
KNA1——————> KUNNR
LFA1 ——————-> LIFNR
KNB1——————-> KUNNR, BUKRS
LFB1——————–> LIFNR, BUKRS
MARA —————–> MATNR
MAKT——————> MATNR, SPRAS
EKKO——————> EBELN
EKPO——————> EBELN, EBELP
MARC—————–> MATNR, WERKS
MARD —————–> MATNR, WERKS, LGORT
T001W —————-> WERKS
T001L——————> WERKS, LGORT
VBAK——————> VBELN
VBAP——————> VBELN, POSNR
Joins:
Joins are used to fetch the data from more than one table.
There are two types of joins.
1. Inner join
2. Left outer join
Inner join:
Inner join in ABAP pick the data from both the tables if & only if there is one or more than one entry is available in the right hand side table with corresponding left hand side table.
Syntax:
Select <data base table 1> ~ <field 1> <data base table 1> ~ <field 2> ——-
<data base table 2> ~ <field 1> <data base table 2> ~ <field 2> ——–
<data base table 3> ~ <field 1> <data base table 3> ~ <field 2> ——–
into table <internal table > from <database table1> inner join <database table 2 > on
<data base table 1> ~ <field > = <data base table 2> ~ <field > inner join <data base table 3> on
<data base table 2> ~ <field> = <data base table 3> ~ <field>…………..Where <condition>.
Objects: Based on the given vendor numbers, to display the vendor numbers,vendor name, purchasing document numbers, document dates, item numbers, quantity, unit of measurements & net price.
Data v1 type lfa1-lifnr.
select-options s_lifnr for v1.
Types: Begin of ty_final,
LIFNR TYPE LFA1-LIFNR,
NAME1 TYPE LFA1-NAME1,
EBELN TYPE EKKO-EBELN,
BEDAT TYPE EKKO-BEDAT,
EBELP TYPE EKPO-EBELP,
MENGE TYPE EKPO-MENGE,
MEINS TYPE EKPO-MEINS,
NETPR TYPE EKPO-NETPR,
end of ty_final.
Data: wa_final type ty_final,
it_final type table of ty_final.
Select LFA1~LIFNR LFA1~NAME1 EKKO~EBELN EKKO~BEDAT EKPO~EBELP EKPO~MENGE EKPO~MEINS EKPO~NETPR into table it_final from LFA1 inner join EKKO on LFA1~LIFNR = EKKO~LIFNR inner join EKPO on EKKO~EBELN = EKPO~EBELN where LFA1~LIFNR in S_lifnr.
Loop at it_final into wa_final.
Write:/ wa_final-lifnr, wa_final-name1, wa_final-ebeln, wa_final-bedat, wa_final-ebelp,
wa_final-menge, wa_final-meins, wa_final-netpr.
Endloop.
Object:-Based on the given customer numbers display the customer numbers, customer names, sales document numbers, document date, item number, material number, material description, quantity, unit of measurement, net price by using inner join.
Data V1 type KNA1-kunnr.
Select-options s_kunnr for V1.
Types: begin of ty_final,
KUNNR type KNA1-KUNNR,
NAME1 type KNA1-NAME1,
VBELN type VBAK-VBELN,
AUDAT type VBAK-AUDAT,
POSNR type VBAP-POSNR,
MATNR type VBAP-MATNR,
MAKTX type MAKT-MAKTX,
KWMENG type VBAP-KWMENG,
MEINS type VBAP-MEINS,
NETWR type VBAP-NETWR,
End of ty_final.
Data: wa_final type ty_final,
it_final
