01-02-2008 11:25 AM
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
01-02-2008 11:29 AM
hi Manish,
use views ,if you dont wanna use inner joins,
other option is to use 'for all entries' in select statement.
Regards,
Talwinder.
01-02-2008 11:26 AM
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.
01-02-2008 11:32 AM
01-02-2008 11:28 AM
01-02-2008 11:28 AM
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.
01-02-2008 11:33 AM
01-02-2008 11:36 AM
Hi,
Usage of For all Entries.
http://www.sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm
01-02-2008 11:29 AM
hi Manish,
use views ,if you dont wanna use inner joins,
other option is to use 'for all entries' in select statement.
Regards,
Talwinder.
01-02-2008 11:31 AM
01-02-2008 11:40 AM
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
01-02-2008 11:51 AM
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.
ENDLOOP.
ENDLOOP.
ENDLOOP.
Reward if usful,
Thanks,
Srikanth.A
01-02-2008 11:58 AM
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..
01-02-2008 12:15 PM
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