12-01-2008 7:00 AM
i want to remove inner join from this selection query ie need to write three differnt select query from this one ...
m new to abap can smbdy guide me in this...
SELECT equnr kunum eaufnr gewrk ilart pm_objty eqmnum
dobjnr fgstrp aufpl d~auart user4
INTO CORRESPONDING FIELDS OF TABLE itab FROM afih AS e
INNER JOIN afko AS f
ON faufnr = eaufnr
INNER JOIN aufk AS d
ON daufnr = eaufnr
INNER JOIN crhd AS c
ON egewrk = cobjid
INNER JOIN pmsdo AS g
ON dobjnr = gobjnr
WHERE e~aufnr IN aufnr
AND e~equnr IN equnr
AND e~ilart IN ilart
AND f~gstrp IN gstrp
AND d~gsber IN gsber
AND c~arbpl IN arbpl
AND c~objty IN objty
AND g~vkbur IN vkbur.
Edited by: nikita mishra on Dec 1, 2008 8:00 AM
12-01-2008 7:20 AM
Hi Nikita,
Instead of inner join use For all entries.
Sample code:
DATA: BEGIN OF wa_final OCCURS 0,
pernr LIKE pa0000-pernr, "Personnel number
ename LIKE pa0001-ename, "Formatted Name of Employee or Applicant
gbdat LIKE pa0002-gbdat, "Date of Birth
begda LIKE pa0001-begda, "Start Date
endda LIKE pa0001-endda, "End Date
pnalt LIKE pa0032-pnalt, "Previous Personnel Number
orgeh LIKE pa0001-orgeh, "Organizational Unit
orgtx LIKE t527x-orgtx, "Short Text of Organizational Unit
plans LIKE pa0001-plans, "Position
plstx LIKE t528t-plstx, "Short Text of Position
gesch LIKE pa0002-gesch, "Gender Key
famst LIKE pa0002-famst, "Marital Status
persk LIKE pa0001-persk, "Employee Subgroup
ptext LIKE t503t-ptext, "Name of Employee Subgroup
btrtl LIKE pa0001-btrtl, "Personnel Subarea
btext LIKE t001p-btext, "Personnel Subarea Text
werks LIKE pa0001-werks, "Personnel Area
shkgz TYPE zzphr-shkzg,
status TYPE string,
mstatus TYPE string, "Marital Status Text
END OF wa_final.
DATA: BEGIN OF wa_final1 OCCURS 0,
shkgz TYPE zzphr-shkzg, "If Date = 31/12/9999-'Y' Else 'N'
pernr LIKE pa0000-pernr, "Personnel number
ename LIKE pa0001-ename, "Formatted Name of Employee or Applicant
gbdat LIKE pa0002-gbdat, "Date of Birth
begda LIKE pa0001-begda, "Start Date
pnalt LIKE pa0032-pnalt, "Previous Personnel Number
werks LIKE pa0001-werks, "Personnel Area
btrtl LIKE pa0001-btrtl, "Personnel Subarea
* btext LIKE t001p-btext, "Personnel Subarea Text
orgtx LIKE t527x-orgtx, "Short Text of Organizational Unit
ptext LIKE t503t-ptext, "Name of Employee Subgroup
plstx LIKE t528t-plstx, "Short Text of Position
* gesch LIKE pa0002-gesch, "Gender Key
* famst LIKE pa0002-famst, "Marital Status
status TYPE string, "01-Male, 02-Female
mstatus TYPE string, "Marital Status Text
END OF wa_final1.
DATA: BEGIN OF wa_pa0001,
pernr LIKE pa0001-pernr,
endda LIKE pa0001-endda,
ename LIKE pa0001-ename,
begda LIKE pa0001-begda,
orgeh LIKE pa0001-orgeh,
plans LIKE pa0001-plans,
persk LIKE pa0001-persk,
btrtl LIKE pa0001-btrtl,
END OF wa_pa0001.
DATA: BEGIN OF wa_pa0000,
pernr LIKE pa0000-pernr,
plans LIKE pa0001-plans,
orgeh LIKE pa0001-orgeh,
persk LIKE pa0001-persk,
btrtl LIKE pa0001-btrtl,
werks LIKE pa0001-werks,
END OF wa_pa0000.
DATA: BEGIN OF wa_pa0002,
pernr LIKE pa0002-pernr,
gbdat LIKE pa0002-gbdat,
gesch LIKE pa0002-gesch,
famst LIKE pa0002-famst,
END OF wa_pa0002.
DATA: BEGIN OF wa_pa0032,
pernr LIKE pa0032-pernr,
pnalt LIKE pa0032-pnalt,
END OF wa_pa0032.
DATA: BEGIN OF wa_t528t,
plans LIKE t528t-plans,
plstx LIKE t528t-plstx,
END OF wa_t528t.
DATA: BEGIN OF wa_t527x,
orgeh LIKE t527x-orgeh,
orgtx LIKE t527x-orgtx,
END OF wa_t527x.
DATA: BEGIN OF wa_t503t,
persk LIKE t503t-persk,
ptext LIKE t503t-ptext,
END OF wa_t503t.
DATA: BEGIN OF wa_t001p,
werks LIKE t001p-werks,
btrtl LIKE t001p-btrtl,
btext LIKE t001p-btext,
END OF wa_t001p.
DATA: BEGIN OF wa_pa0062,
pernr LIKE pa0062-pernr,
famst LIKE pa0062-famst,
END OF wa_pa0062.
DATA: BEGIN OF it_text OCCURS 0,
text(256),
END OF it_text.
****for tab delimiter
***CONSTANTS : c_tab TYPE x VALUE '09'.
DATA: it_pa0001 LIKE STANDARD TABLE OF wa_pa0001 WITH HEADER LINE,
it_pa0002 LIKE STANDARD TABLE OF wa_pa0002 WITH HEADER LINE,
it_pa0000 LIKE STANDARD TABLE OF wa_pa0000 WITH HEADER LINE,
it_pa0032 LIKE STANDARD TABLE OF wa_pa0032 WITH HEADER LINE,
it_pa0062 LIKE STANDARD TABLE OF wa_pa0062 WITH HEADER LINE,
it_t528t LIKE STANDARD TABLE OF wa_t528t WITH HEADER LINE,
it_t527x LIKE STANDARD TABLE OF wa_t527x WITH HEADER LINE,
it_t503t LIKE STANDARD TABLE OF wa_t503t WITH HEADER LINE,
it_t001p LIKE STANDARD TABLE OF wa_t001p WITH HEADER LINE,
it_final LIKE STANDARD TABLE OF wa_final WITH HEADER LINE,
it_final1 LIKE STANDARD TABLE OF wa_final1 WITH HEADER LINE.
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001.
PARAMETERS: p_user(30) DEFAULT 'savior' OBLIGATORY,
p_pwd(30) DEFAULT 'save' OBLIGATORY,
p_host(64) DEFAULT '192.168.0.10' OBLIGATORY.
SELECTION-SCREEN END OF BLOCK blk1.
SELECTION-SCREEN BEGIN OF BLOCK blk2 WITH FRAME TITLE text-002.
PARAMETERS: p_file LIKE rlgrap-filename default '1511081230.TXT' obligatory.
SELECTION-SCREEN END OF BLOCK blk2.
SELECT pernr
plans
orgeh
persk
btrtl
werks
FROM pa0001
INTO TABLE it_pa0000.
IF it_pa0000[] IS NOT INITIAL.
SELECT pernr
endda
ename
begda
orgeh
plans
persk
btrtl
FROM pa0001 INTO TABLE it_pa0001
FOR ALL ENTRIES IN it_pa0000
WHERE pernr = it_pa0000-pernr.
SELECT pernr
gbdat
gesch
famst
FROM pa0002 INTO TABLE it_pa0002
FOR ALL ENTRIES IN it_pa0000
WHERE pernr = it_pa0000-pernr.
SELECT pernr
pnalt
FROM pa0032 INTO TABLE it_pa0032
FOR ALL ENTRIES IN it_pa0000
WHERE pernr = it_pa0000-pernr.
SELECT plans
plstx
FROM t528t INTO TABLE it_t528t
FOR ALL ENTRIES IN it_pa0000
WHERE plans = it_pa0000-plans.
SELECT orgeh
orgtx
FROM t527x INTO TABLE it_t527x
FOR ALL ENTRIES IN it_pa0000
WHERE orgeh = it_pa0000-orgeh.
SELECT persk
ptext
FROM t503t INTO TABLE it_t503t
FOR ALL ENTRIES IN it_pa0000
WHERE persk = it_pa0000-persk.
SELECT werks
btrtl
btext
FROM t001p INTO TABLE it_t001p
FOR ALL ENTRIES IN it_pa0000
WHERE btrtl = it_pa0000-btrtl
AND werks = it_pa0000-werks.
SELECT pernr
famst
FROM pa0062 INTO TABLE it_pa0062
FOR ALL ENTRIES IN it_pa0000
WHERE pernr = it_pa0000-pernr.
ENDIF.
LOOP AT it_pa0000.
it_final-pernr = it_pa0000-pernr.
READ TABLE it_pa0001 WITH KEY pernr = it_pa0000-pernr.
IF sy-subrc = 0.
it_final-endda = it_pa0001-endda.
it_final-ename = it_pa0001-ename.
it_final-begda = it_pa0001-begda.
it_final-orgeh = it_pa0001-orgeh.
it_final-plans = it_pa0001-plans.
it_final-persk = it_pa0001-persk.
it_final-btrtl = it_pa0001-btrtl.
it_final-werks = it_pa0000-werks.
ENDIF.
READ TABLE it_pa0002 WITH KEY pernr = it_pa0000-pernr.
IF sy-subrc = 0.
it_final-gbdat = it_pa0002-gbdat.
it_final-gesch = it_pa0002-gesch.
it_final-famst = it_pa0002-famst.
ENDIF.
READ TABLE it_pa0032 WITH KEY pernr = it_pa0000-pernr.
IF sy-subrc = 0.
it_final-pnalt = it_pa0032-pnalt.
ENDIF.
READ TABLE it_t528t WITH KEY plans = it_final-plans.
IF sy-subrc = 0.
it_final-plstx = it_t528t-plstx.
ENDIF.
READ TABLE it_t527x WITH KEY orgeh = it_final-orgeh.
IF sy-subrc = 0.
it_final-orgtx = it_t527x-orgtx.
ENDIF.
READ TABLE it_t503t WITH KEY persk = it_final-persk.
IF sy-subrc = 0.
it_final-ptext = it_t503t-ptext.
ENDIF.
READ TABLE it_t001p WITH KEY btrtl = it_final-btrtl werks = it_final-werks.
IF sy-subrc = 0.
it_final-btext = it_t001p-btext.
ENDIF.
READ TABLE it_pa0062 WITH KEY pernr = it_pa0000-pernr.
IF sy-subrc = 0.
it_final-famst = it_pa0062-famst.
ENDIF.
APPEND it_final.
CLEAR: it_final, it_pa0000, it_pa0001, it_pa0002, it_pa0032, it_t528t, it_t527x, it_t503t, it_t001p, it_pa0062.
ENDLOOP.
Regards,
AK
12-01-2008 9:36 AM
Hi Nikita,
Plz try ths way :
types: begin of ty_final,
equnr type afih-equnr,
kunum type afih-kunum,
aufnr type afih-aufnr,
gewrk type afih-gewrk,
ilart type afih-ilart,
pm_objty type afih-pm_objty,
qmnum type afih-qmnum,
gstrp type afko-gstrp,
aufpl type afko-aufpl,
objnr type aufk-objnr,
auart type aufk-auart,
user4 type aufk-user4,
objid type crhd-objid,
arbpl type crhd-arbpl,
objty type crhd-objty,
vkbur type pmsdo-vkbur,
end of ty_final.
types: begin of ty_aufk,
aufnr type aufk-aufnr,
objnr type aufk-objnr,
auart type aufk-auart,
user4 type aufk-user4,
end of ty_aufk.
types: begin of ty_crhd,
objid type crhd-objid,
arbpl type crhd-arbpl,
objty type crhd-objty,
end of ty_crhd.
types: begin of ty_pmsdo,
objnr type pmsdo-objnr,
vkbur type pmsdo-vkbur,
end of ty_pmsdo.
types: begin of ty_afko,
aufnr type afko-aufnr,
gstrp type afko-gstrp,
aufpl type afko-aufpl,
end of ty_afko.
types: begin of ty_afih,
equnr type afih-equnr,
kunum type afih-kunum,
aufnr type afih-aufnr,
gewrk type afih-gewrk,
ilart type afih-ilart,
pm_objty type afih-pm_objty,
qmnum type afih-qmnum,
end of ty_afih.
data: it_afih type standard table of ty_afih,
wa_afih like line of it_afih,
it_afko type standard table of ty_afko,
wa_afko like line of it_afko,
it_pmsdo type standard table of ty_pmsdo,
wa_pmsdo like line of it_pmsdo,
it_crhd type standard table of ty_crhd,
wa_crhd like line of it_crhd,
it_aufk type standard table of ty_aufk,
wa_aufk like line of it_aufk,
it_final type standard table of ty_final,
wa_final like line of it_final.
select-options:
aufnr for afko-aufnr,
equnr for afih-equnr,
ilart for afih-ilart,
gstrp for afko-gstrp,
gsber for aufk-gsber,
arbpl for crhd-arbpl,
objty for crhd-objty,
vkbur for pmsdo-vkbur.
Start-of-selection.
select aufnr
objnr
auart
user4
from aufk
into table it_aufk
where aufnr IN aufnr.
if not it_aufk is initial.
select equnr
kunum
aufnr
gewrk
ilart
pm_objty
qmnum
from afih
into table it_afih
for all entries in it_aufk
where aufnr = it_aufk-aufnr
and equnr in equnr
and ilart in ilart.
if not it_afih is initial.
select gstrp
aufpl
from afko
into table it_afko
for all entries in it_afih
where aufnr = it_afih-aufnr
and gstrp IN gstrp.
endif.
endif.
if not it_afih is initial.
select objid
arbpl
objty
from crhd
into table it_crhd
for all entries in it_afih
where objid = it_afih-gewrk
and arbpl IN arbpl
and objty in objty.
endif.
if not it_aufk is initial.
select objnr
vkbur
from pmsdo
into table it_pmsdo
for all entries in it_aufk
where objnr = it_aufk-objnr
and vkbur IN vkbur.
endif.
loop at it_aufk into wa_aufk.
read table it_afih into wa_afih with key aufnr = wa_aufk-aufnr.
read table it_afko into wa_afko with key aufnr = wa_aufk-aufnr.
read table it_pmsdo into wa_pmsdo with key objnr = wa_aufk-objnr.
read table it_crhd into wa_crhd with key objid = wa_afih-gewrk.
wa_final-equnr = wa_afih-equnr.
wa_final-kunum = wa_afih-kunum.
wa_final-aufnr = wa_afih-aufnr.
wa_final-gewrk = wa_afih-gewrk.
wa_final-ilart = wa_afih-ilart.
wa_final-pm_objty = wa_afih-pm_objty.
wa_final-qmnum = wa_afih-qmnum.
wa_final-gstrp = wa_afko-gstrp.
wa_final-aufpl = wa_afko-aufpl.
wa_final-objnr = wa_aufk-objnr.
wa_final-auart = wa_aufk-auart.
wa_final-user4 = wa_aufk-user4.
wa_final-objid = wa_crhd-objid.
wa_final-arbpl = wa_crhd-arbpl.
wa_final-objty = wa_crhd-objty.
wa_final-vkbur = wa_pmsdo-vkbur.
append wa_final to it_final.
endloop.
hope this will solve your problem.
Thanks,
Dhanashri Pawar
12-01-2008 10:45 AM
TABLES : afih,
afko,
aufk,
crhd,
pmsdo
data : it_afih type standard table of afih,
it_afko type standard table of afko,
it_aufk type standard table of aufk,
it_crhd type standard table of crhd,
it_pmsdo type standard table of pmsdo,
data : wa_afih type afih,
wa_afko type afko,
wa_aufk type aufk,
wa_crhd type crhd,
wa_pmsdo type pmsdo.
parameters : p_aufnr type aufk-aufnr.
select * from afih into table it_afih
where aufnr = p_aufnr.
if sy-subrc eq 0.
sort it_afih by aufnr.
select * from afko into table it_afko
for all entries in it_afih
where aufnr = it_afih-aufnr.
endif.
if it_afko is not initial.
sort it_afko by aufnr.
select * from afko into table it_afko
for all entries in it_afih
where aufnr = it_afih-aufnr.
endif.
like that u can write the Query using FOR ALL ENTRIES for your TABLES
12-01-2008 10:55 AM
hi
1) first select statement to itab1
2) second select statement use itab1 in FOR all entries addition & related where condition: move to itab2
3) third select statement use itab2 in FOR all entries addition & related where condition: move to itab3
now itab3 is the final itab after the join
12-01-2008 11:09 AM
select * from afih into corresponding fields of
table it_afih.
IF it_afih[] is not initial.
select * from afko into corresponding fields of
table it_afko for all entries in
table it_afih
where aufnr = it_afih-aufnr.
ENDIF.
loop at it_afih.
read it_afko with key aufnr = it_Afih-aufnr.
wa_final-aufnr = it_afko-aufnr.
like this
append wa_final to it_final.
IF it_final[] is not initial.
select * from aufk into corresponding fields of table
it_aufk for all entries in it_final
where aufnr = it_final-aufnr.
ENDIF.
****use loop like above to move all fields to one internal table.
12-01-2008 11:32 AM
Hi,
SELECT aufnr equnr qmnum FROM afih into table itab
WHERE aufnr IN aufnr
AND equnr IN equnr
AND ilart IN ilart.
if itab is not initial.
SELECT objnr auart from aufk into table itab1 for all entries in itab on aufnr = itab-aufnr
WHERE gsber IN gsber.
endif.
See above select Quary ,fallow same like other tables.....
12-01-2008 12:38 PM
Hi nikita,
try using database view of three tables instaed of inner join.
thanks
Arjun
12-01-2008 9:13 PM
u got 5 tables but just want 3 selects? sounds like some inner joins will remain.
Do u wanna change it just for better understanding or to increase the performance?
Regards
Stefan Seeburger