topic Re: access different database tables using single select statemetnt in Application Development and Automation Discussions

access different database tables using single select statemetnt

Former Member — Fri, 18 May 2007 04:18:54 GMT

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.

Re: access different database tables using single select statemetnt

Former Member — Fri, 18 May 2007 04:22:47 GMT

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 b_{vbeln = a}vbeln

join vbap as c

on b_{aubel = c}vbeln and

b_{aupos = c}posnr

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

Re: access different database tables using single select statemetnt

Former Member — Fri, 18 May 2007 04:29:25 GMT

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 ekko_{ebeln ekpo}ebelp ekpo_{matnr makt}maktx ekpo~werks

ekpo_{menge ekpo}netpr ekko~lifnr

INTO CORRESPONDING FIELDS OF TABLE t_itab

FROM ( ekko INNER JOIN ekpo ON ekko_{ebeln = ekpo}ebeln ) INNER

JOIN makt ON makt_{matnr = ekpo}matnr 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

Re: access different database tables using single select statemetnt

Former Member — Fri, 18 May 2007 06:20:37 GMT

hi,

you can use joins for selecting fields frommultiple tables.

Reward with points if helpful.

Message was edited by:

Vinutha YV