topic Re: retrive data from 4 tables without using joins in Application Development and Automation Discussions

retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:25:21 GMT

hi ,

i have to retrive data from 4 tables........but i donot want to use joins because of performance issues.

please guide me how to proceed further ?

thanks

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:26:58 GMT

Hi,

There r two solutions for u r question I think:-

1. Use the FOR ALL ENTRIES select statement.

2. Use the VIEWS (ie DATABASE VIEW) on those 4 tables and

Select data from that view.

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:28:25 GMT

Hi,

Try using For all entries.

Thanks,

Sriram Ponna.

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:28:28 GMT

hi,

then use select query put it in an internal table and use the second query with for all entries and do the process for the remaining two..

which are the tables are u trying with can u plz specify...

regards,

karthik.

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:29:44 GMT

hi Manish,

use views ,if you dont wanna use inner joins,

other option is to use 'for all entries' in select statement.

Regards,

Talwinder.

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:31:53 GMT

u can use views. that is the way. r,

For all entries

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:32:29 GMT

please send me some syntex.. for little help

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:33:20 GMT

please send soem reference syntex

Re: retrive data from 4 tables without using joins

former_member188829 — Wed, 02 Jan 2008 11:36:57 GMT

Hi,

Usage of For all Entries.

http://www.sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:40:54 GMT

Hi Manish,

This can be done by using FOR ALL ENTRIES STATEMENT.One of the necessary condition is one FIELD MUST be in common.I will send a sample code for u check it once..

tables: vbak.

data: begin of itab occurs 0,

vbeln like vbak-vbeln,

erdat like vbak-erdat,

end of itab.

data: begin of itab1 occurs 0,

vbeln like vbak-vbeln,

posnr like vbap-posnr,

end of itab1.

data: begin of itab2 occurs 0,

vbeln like vbak-vbeln,

meins like vbrp-meins,

end of itab2.

select-option: s_vbeln for vbak-vbeln.

select vbeln

erdat

from vbak

into table itab

where vbeln in s_vbeln.

select vbeln

posnr

from vbap

into table itab1

for all entries in itab

where vbeln = itab-vbeln.

select vbeln

meins

from vbrp

into table itab2

for all entries in itab

where vbeln = itab-vbeln.

Award Points If helpful.

Kiran Kumar.G

Have a Nice Day..

Edited by: KIRAN KUMAR on Jan 2, 2008 12:42 PM

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:51:03 GMT

hi manish,

CONSTANTS: c_act_plan(2) TYPE c VALUE 'U1' ,

c_person(1) TYPE c VALUE 'P' ,

p1_betid(8) TYPE c VALUE '50005316',

c_topdown(1) TYPE c VALUE 'A' ,

c_admnby(3) TYPE c VALUE '032' ,

c_bet_type(1) TYPE c VALUE 'Q' ,

c_firmed(1) TYPE c VALUE '1' ,

c_subtyp_mail(4) TYPE c VALUE '0001' .

TYPES: BEGIN OF ty_hrp1001,

objid TYPE hrp1001-objid,

END OF ty_hrp1001,

BEGIN OF ty_pa0001,

pernr TYPE pa0001-pernr,

subty TYPE pa0001-subty,

objps TYPE pa0001-objps,

sprps TYPE pa0001-sprps,

endda TYPE pa0001-endda,

begda TYPE pa0001-begda,

seqnr TYPE pa0001-seqnr,

werks TYPE pa0001-werks,

gsber TYPE pa0001-gsber,

btrtl TYPE pa0001-btrtl,

END OF ty_pa0001,

BEGIN OF ty_t500p,

persa TYPE t500p-persa,

name1 TYPE t500p-name1,

END OF ty_t500p,

BEGIN OF ty_t001p,

werks TYPE t001p-werks,

btrtl TYPE t001p-btrtl,

btext TYPE t001p-btext,

END OF ty_t001p,

BEGIN OF ty_pa0002,

pernr TYPE pa0002-pernr,

subty TYPE pa0002-subty,

objps TYPE pa0002-objps,

sprps TYPE pa0002-sprps,

endda TYPE pa0002-endda,

begda TYPE pa0002-begda,

seqnr TYPE pa0002-seqnr,

nachn TYPE pa0002-nachn,

vorna TYPE pa0002-vorna,

END OF ty_pa0002,

BEGIN OF ty_pa0105,

pernr TYPE pa0105-pernr ,

subty TYPE pa0105-subty ,

objps TYPE pa0105-objps ,

sprps TYPE pa0105-sprps ,

endda TYPE pa0105-endda ,

begda TYPE pa0105-begda ,

seqnr TYPE pa0105-seqnr ,

usrid_long TYPE pa0105-usrid_long,

END OF ty_pa0105,

BEGIN OF ty_output,

pernr TYPE pa0002-pernr ,

werks TYPE pa0001-werks ,

gsber TYPE pa0001-gsber ,

btrtl TYPE pa0001-btrtl ,

name1 TYPE t500p-name1 ,

btext TYPE t001p-btext ,

nachn TYPE pa0002-nachn ,

vorna TYPE pa0002-vorna ,

usrid_long TYPE pa0105-usrid_long,

END OF ty_output.

DATA: w_hrp1001 TYPE ty_hrp1001,

t_hrp1001 TYPE TABLE OF ty_hrp1001,

t_hrp1001_tmp TYPE TABLE OF ty_hrp1001,

w_pa0001 TYPE ty_pa0001 ,

t_pa0001 TYPE SORTED TABLE OF ty_pa0001

WITH NON-UNIQUE KEY pernr ,

t_pa0001_tmp TYPE TABLE OF ty_pa0001 ,

w_t500p TYPE ty_t500p ,

t_t500p TYPE HASHED TABLE OF ty_t500p

WITH UNIQUE KEY persa ,

w_t001p TYPE ty_t001p ,

t_t001p TYPE HASHED TABLE OF ty_t001p

WITH UNIQUE KEY werks btrtl ,

w_pa0002 TYPE ty_pa0002 ,

t_pa0002 TYPE SORTED TABLE OF ty_pa0002

WITH NON-UNIQUE KEY pernr ,

t_pa0002_tmp TYPE TABLE OF ty_pa0002 ,

w_pa0105 TYPE ty_pa0105 ,

t_pa0105 TYPE SORTED TABLE OF ty_pa0105

WITH NON-UNIQUE KEY pernr ,

w_output TYPE ty_output ,

t_output TYPE TABLE OF ty_output .

SELECT objid

FROM hrp1001

INTO TABLE t_hrp1001

WHERE otype EQ c_person

AND plvar EQ c_act_plan

AND rsign EQ c_topdown

AND relat EQ c_admnby

AND istat EQ c_firmed

AND begda LE sy-datum

AND endda GT sy-datum

AND sclas EQ c_bet_type

AND sobid EQ p1_betid.

IF sy-subrc EQ 0.

SORT t_hrp1001 BY objid.

ENDIF.

IF NOT t_hrp1001[] IS INITIAL.

t_hrp1001_tmp[] = t_hrp1001[].

DELETE ADJACENT DUPLICATES FROM t_hrp1001_tmp

COMPARING objid.

SELECT pernr

subty

objps

sprps

endda

begda

seqnr

werks

gsber

btrtl

FROM pa0001

INTO TABLE t_pa0001

FOR ALL ENTRIES IN t_hrp1001_tmp

WHERE pernr EQ t_hrp1001_tmp-objid

AND endda GT sy-datum

AND begda LE sy-datum.

SELECT pernr

subty

objps

sprps

endda

begda

seqnr

nachn

vorna

FROM pa0002

INTO TABLE t_pa0002

FOR ALL ENTRIES IN t_hrp1001_tmp

WHERE pernr EQ t_hrp1001_tmp-objid

AND endda GT sy-datum

AND begda LE sy-datum.

ENDIF.

IF NOT t_pa0001[] IS INITIAL.

t_pa0001_tmp[] = t_pa0001[].

SORT t_pa0001_tmp BY werks btrtl.

DELETE ADJACENT DUPLICATES FROM t_pa0001_tmp COMPARING werks btrtl.

SELECT werks

btrtl

btext

FROM t001p

INTO TABLE t_t001p

FOR ALL ENTRIES IN t_pa0001_tmp

WHERE werks EQ t_pa0001_tmp-werks

AND btrtl EQ t_pa0001_tmp-btrtl.

DELETE ADJACENT DUPLICATES FROM t_pa0001_tmp COMPARING werks.

SELECT persa

name1

FROM t500p

INTO TABLE t_t500p

FOR ALL ENTRIES IN t_pa0001_tmp

WHERE persa EQ t_pa0001_tmp-werks.

ENDIF.

IF NOT t_pa0002[] IS INITIAL.

t_pa0002_tmp[] = t_pa0002[].

DELETE ADJACENT DUPLICATES FROM t_pa0002_tmp COMPARING pernr.

SELECT pernr

subty

objps

sprps

endda

begda

seqnr

usrid_long

FROM pa0105

INTO TABLE t_pa0105

FOR ALL ENTRIES IN t_pa0002_tmp

WHERE pernr EQ t_pa0002_tmp-pernr

AND subty EQ c_subtyp_mail

AND endda GT sy-datum

AND begda LE sy-datum

AND usrty EQ c_subtyp_mail.

ENDIF.

REFRESH t_output.

LOOP AT t_hrp1001 INTO w_hrp1001.

LOOP AT t_pa0001 INTO w_pa0001

WHERE pernr EQ w_hrp1001-objid.

READ TABLE t_t500p INTO w_t500p WITH KEY persa = w_pa0001-werks

TRANSPORTING

name1.

CHECK sy-subrc EQ 0.

READ TABLE t_t001p INTO w_t001p WITH KEY werks = w_pa0001-werks

btrtl = w_pa0001-btrtl

TRANSPORTING

btext.

LOOP AT t_pa0002 INTO w_pa0002

WHERE pernr EQ w_hrp1001-objid.

LOOP AT t_pa0105 INTO w_pa0105

WHERE pernr EQ w_pa0002-pernr.

w_output-pernr = w_pa0002-pernr .

w_output-werks = w_pa0001-werks .

w_output-gsber = w_pa0001-gsber .

w_output-btrtl = w_pa0001-btrtl .

w_output-name1 = w_t500p-name1 .

w_output-btext = w_t001p-btext .

w_output-nachn = w_pa0002-nachn .

w_output-vorna = w_pa0002-vorna .

w_output-usrid_long = w_pa0105-usrid_long.

APPEND w_output TO t_output.

CLEAR w_output.

ENDLOOP.

Reward if usful,

Thanks,

Srikanth.A

Re: retrive data from 4 tables without using joins

Former Member — Wed, 02 Jan 2008 11:58:26 GMT

hi,

You can do with For all entries addition.

******************************************

REPORT zstemp_qty2_ .

TABLES:mara.

DATA:it_makt LIKE makt OCCURS 0 WITH HEADER LINE.

DATA:it_ekko LIKE ekko OCCURS 0 WITH HEADER LINE.

DATA:it_ekpo LIKE ekpo OCCURS 0 WITH HEADER LINE.

DATA:it_vbap LIKE vbap OCCURS 0 WITH HEADER LINE.

START-OF-SELECTION.

SELECT * FROM ekko INTO TABLE it_ekko

UP TO 100 ROWS.

IF NOT it_ekko[] IS INITIAL.

SELECT * FROM ekpo INTO TABLE it_ekpo

FOR ALL ENTRIES IN it_ekko

WHERE ebeln = it_ekko-ebeln.

ENDIF.

SORT it_ekpo BY ebeln.

DELETE ADJACENT DUPLICATES FROM it_ekpo COMPARING ebeln.

IF NOT it_ekpo[] IS INITIAL.

SELECT * FROM makt INTO TABLE it_makt

FOR ALL ENTRIES IN it_ekpo

WHERE matnr = it_ekpo-matnr.

ENDIF.

IF NOT it_makt[] IS INITIAL.

SELECT * FROM vbap INTO TABLE it_vbap

FOR ALL ENTRIES IN it_makt

WHERE matnr = it_makt-matnr.

ENDIF.

LOOP AT it_makt.

READ TABLE it_vbap WITH KEY matnr = it_makt-matnr.

IF sy-subrc = 0.

WRITE:/ it_vbap-matnr,it_makt-maktx.

ENDIF.

ENDLOOP.

********************************

this is just sample example .then you can replace tables with your reqt.

Regds

Sivaparvathi

Please reward points if helpful..

Re: retrive data from 4 tables without using joins

ThomasZloch — Wed, 02 Jan 2008 12:15:00 GMT

and why not use joins? If qualifed keys are being used, joining 4 tables should not be a problem, and you can fill your result set in one go rather than using a clumsy 4-step approach with FOR ALL ENTRIES.

some people recommended using views, these are nothing else than DDIC-predefined joins.

Cheers

Thomas