topic Re: problem with selection query in Application Development and Automation Discussions

problem with selection query

Former Member — Mon, 01 Dec 2008 07:00:37 GMT

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 e_{aufnr gewrk ilart pm_objty e}qmnum

d_{objnr f}gstrp aufpl d~auart user4

INTO CORRESPONDING FIELDS OF TABLE itab FROM afih AS e

INNER JOIN afko AS f

ON f_{aufnr = e}aufnr

INNER JOIN aufk AS d

ON d_{aufnr = e}aufnr

INNER JOIN crhd AS c

ON e_{gewrk = c}objid

INNER JOIN pmsdo AS g

ON d_{objnr = g}objnr

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

Re: problem with selection query

ak_upadhyay — Mon, 01 Dec 2008 07:20:19 GMT

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,

Re: problem with selection query

Former Member — Mon, 01 Dec 2008 09:36:57 GMT

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

Re: problem with selection query

Former Member — Mon, 01 Dec 2008 10:45:41 GMT

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

Re: problem with selection query

Former Member — Mon, 01 Dec 2008 10:55:02 GMT

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

Re: problem with selection query

Former Member — Mon, 01 Dec 2008 11:09:15 GMT

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.

Re: problem with selection query

Former Member — Mon, 01 Dec 2008 11:32:18 GMT

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.....

Re: problem with selection query

arjun_subhash — Mon, 01 Dec 2008 12:38:18 GMT

Hi nikita,

try using database view of three tables instaed of inner join.

thanks

Arjun

Re: problem with selection query

Former Member — Mon, 01 Dec 2008 21:13:19 GMT

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