Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

access different database tables using single select statemetnt

Former Member
0 Kudos
103

how can i access different database tables in single select statemetn where the two tables do not have a primary key.

please give me a sample code for it. Actually i want to access different tables through the fields specified in the selection screen.

1 ACCEPTED SOLUTION

Former Member
0 Kudos
78

Hi,

Check this,

SELECT-OPTIONS:

s_matnr FOR mara-matnr,

s_werks FOR ekpo-werks,

s_lifnr FOR ekko-lifnr OBLIGATORY,

s_ekorg FOR ekko-ekorg OBLIGATORY,

s_ekgrp FOR ekko-ekgrp OBLIGATORY,

s_bedat FOR ekko-bedat OBLIGATORY,

s_ebeeln FOR ekko-ebeln ,

s_ebelp FOR ekpo-ebelp .

*----


>START OF SELECTION

START-OF-SELECTION.

SELECT ekkoebeln ekpoebelp ekpomatnr maktmaktx ekpo~werks

ekpomenge ekponetpr ekko~lifnr

INTO CORRESPONDING FIELDS OF TABLE t_itab

FROM ( ekko INNER JOIN ekpo ON ekkoebeln = ekpoebeln ) INNER

JOIN makt ON maktmatnr = ekpomatnr WHERE

ekpo~matnr IN s_matnr AND

ekpo~werks IN s_werks AND

ekko~lifnr IN s_lifnr AND

ekko~ekorg IN s_ekorg AND

ekko~ekgrp IN s_ekgrp AND

ekko~bedat IN s_bedat AND

ekko~ebeln IN s_ebeeln AND

ekpo~ebelp IN s_ebelp .

"selects the output data from ekko,ekpo and makt tables and stores in

"t_itab

"

3 REPLIES 3

Former Member
0 Kudos
78

Hi

By using Joins between the tables we can access more number of tables in a single select

But the tables should have some linked/connecting fields between them, not exactly the primary key fields

see the sample code using joins

select

a~vbeln " Billing Doc Number

a~fktyp " Billing Category

a~vbtyp " Sales Doc category

a~fkdat " Billing doc date

a~fkart " Billing doc type

a~bukrs " Company code

a~kurrf " Exchange rate

a~knumv " Condition record Number

a~waerk " Currency

a~kunag " Sold to Party

b~vrkme " Sales Unit

b~posnr " Item Number

b~charg " Batch Number

b~fkimg " Billed quantity

b~werks " Plant

b~matnr " Material Number

b~netwr " Net Value of Bill Doc

b~wavwr " Cost in Doc Currency

c~kdmat " Customer Material

into table itab_bill

from vbrk as a join vbrp as b

on bvbeln = avbeln

join vbap as c

on baubel = cvbeln and

baupos = cposnr

where a~vbeln in s_vbeln and

a~fkdat in s_fkdat and

a~bukrs in s_bukrs and

a~vtweg in s_vtweg and

a~vkorg in s_vkorg and

a~spart in s_spart and

a~fkart in s_fkart and

b~werks in s_werks and

a~kunag in s_kunag and

a~sfakn eq ' ' and

a~fksto eq ' ' .

Reward points if useful

Regards

Anji

Former Member
0 Kudos
79

Hi,

Check this,

SELECT-OPTIONS:

s_matnr FOR mara-matnr,

s_werks FOR ekpo-werks,

s_lifnr FOR ekko-lifnr OBLIGATORY,

s_ekorg FOR ekko-ekorg OBLIGATORY,

s_ekgrp FOR ekko-ekgrp OBLIGATORY,

s_bedat FOR ekko-bedat OBLIGATORY,

s_ebeeln FOR ekko-ebeln ,

s_ebelp FOR ekpo-ebelp .

*----


>START OF SELECTION

START-OF-SELECTION.

SELECT ekkoebeln ekpoebelp ekpomatnr maktmaktx ekpo~werks

ekpomenge ekponetpr ekko~lifnr

INTO CORRESPONDING FIELDS OF TABLE t_itab

FROM ( ekko INNER JOIN ekpo ON ekkoebeln = ekpoebeln ) INNER

JOIN makt ON maktmatnr = ekpomatnr WHERE

ekpo~matnr IN s_matnr AND

ekpo~werks IN s_werks AND

ekko~lifnr IN s_lifnr AND

ekko~ekorg IN s_ekorg AND

ekko~ekgrp IN s_ekgrp AND

ekko~bedat IN s_bedat AND

ekko~ebeln IN s_ebeeln AND

ekpo~ebelp IN s_ebelp .

"selects the output data from ekko,ekpo and makt tables and stores in

"t_itab

"

Former Member
0 Kudos
78

hi,

you can use joins for selecting fields frommultiple tables.

Reward with points if helpful.

Message was edited by:

Vinutha YV