Select Statement.

[Former Member]

Hi,

Need your help.

I have to write a select statement which should have data keeping

in mind the following details

1. production order no

2. sales order no

3. date.

and then the part no for the following programs.

I have written the following statement, but its taking too

long to execute as well as some one told me to divide the prg into 3 or 2 half.

the select statment is not working kindly help me out please.

the statement is as follows:

data:

S_SHPDATE LIKE s_date.

S_shpdate = s_date - 5.

*SELECT DISTINCT AFKO_{AUFNR AFKO}PLNBEZ AFPO_{PSMNG AFPO}DGLTP

• MARA_{BISMT VBAK}VBELN VBAK_{VDATU VBAP}KWMENG

• VBEP~MBDAT

• INTO CORRESPONDING FIELDS OF ITAB

• FROM AFKO

• INNER JOIN AFPO ON AFPO_{AUFNR = AFPO}AUFNR

• INNER JOIN MARA ON MARA_{MATNR = AFKO}PLNBEZ

• INNER JOIN VBAP ON VBAP_{MATNR = MARA}MATNR

• INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

• INNER JOIN VBEP ON VBEP_{VBELN = VBAP}VBELN

• INNER JOIN VBUK ON VBUK_{VBELN = VBAP}VBELN

• WHERE AFKO~GLTRP >= S_SHPDATE

• AND AFKO~GLTRP <= S_DATE.

SELECT AFKO_{PLNBEZ AFKO}AUFNR MARA_{BISMT AFPO}PSMNG AFPO~DGLTP

FROM AFKO

INNER JOIN AFPO ON AFPO_{AUFNR = AFKO}AUFNR

INNER JOIN MARA ON MARA_{MATNR = AFKO}PLNBEZ

into corresponding fields of ITAB1

where AFPO~DGLTP >= S_SHPDATE

AND AFPO~DGLTP <= S_DATE.

append itab1.

*refresh ITAB1[].

ITAB2[] = ITAB1[].

*sort ITAB2 by PLNBEZ.

delete adjacent duplicates from itab1 comparing PLNBEZ.

check not itab2[] is initial.

Select VBAK_{VBELN VBAK}VDATU VBAP_{KWMENG VBAP}MATNR VBEP~MBDAT

from VBAP

INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN VBEP ON VBEP_{VBELN = VBAP}VBELN

into ITAB3

for all entries in ITAB2

where VBAP~MATNR = ITAB2-MATNR.

append itab3.

[Former Member]

Hi Kamlesh,

Do this way:

SELECT AFKO_{PLNBEZ AFKO}AUFNR MARA_{BISMT AFPO}PSMNG AFPO~DGLTP

FROM AFKO

INNER JOIN AFPO ON AFPO_{AUFNR = AFKO}AUFNR

INNER JOIN MARA ON MARA_{MATNR = AFKO}PLNBEZ

into corresponding fields of <b>table</b> ITAB1

where AFPO~DGLTP >= S_SHPDATE

AND AFPO~DGLTP <= S_DATE.

<b>*append itab1.</b>

*refresh ITAB1[].

ITAB2[] = ITAB1[].

*sort ITAB2 by PLNBEZ.

delete adjacent duplicates from itab1 comparing PLNBEZ.

check not itab2[] is initial.

Select VBAK_{VBELN VBAK}VDATU VBAP_{KWMENG VBAP}MATNR VBEP~MBDAT

from VBAP

INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN VBEP ON VBEP_{VBELN = VBAP}VBELN

<b>into corresponding fields of table</b> ITAB3

for all entries in ITAB2

where VBAP~MATNR = ITAB2-MATNR.

<b>*append itab3.</b>

Hope this will help you.

Regards,

Vivek

PS: Award points if helpful

[Former Member]

Kamlesh,

U are surely on the right track...

SELECT AFKO_{PLNBEZ AFKO}AUFNR MARA_{BISMT AFPO}PSMNG AFPO~DGLTP

FROM AFKO

INNER JOIN AFPO ON AFPO_{AUFNR = AFKO}AUFNR

INNER JOIN MARA ON MARA_{MATNR = AFKO}PLNBEZ

into corresponding fields of table ITAB1

where AFPO~DGLTP >= S_SHPDATE

AND AFPO~DGLTP <= S_DATE.

ITAB2[] = ITAB1[].

sort ITAB2 by PLNBEZ.

delete adjacent duplicates from itab2 comparing PLNBEZ.

check not itab2[] is initial.

Select VBAK_{VBELN VBAK}VDATU VBAP_{KWMENG VBAP}MATNR VBEP~MBDAT

from VBAP

INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN VBEP ON VBEP_{VBELN = VBAP}VBELN

into table ITAB3

for all entries in ITAB2

where VBAP~MATNR = ITAB2-MATNR.

--Please note that if you have lots of data to fetch that it is surely going to effect the performance.

Regards

Anurag

[Former Member]

Hi Anurag,

The second select statement is taking lot of time can u break it into another select statement.

or

can we write another select statement keeping in mind the same logic.

Require ur help.

Regards,

Kamlesh

[Former Member]

I don't think you can fix that select the way it is written. Instead of going to VBAP, you can get the SO directly from table VAPMA.

Rob

[Former Member]

So you can add something like:

TABLES: vapma.

DATA: BEGIN OF itab_mat OCCURS 0,
        vbeln LIKE vapma-vbeln,
      END   OF itab_mat.

SELECT vbeln
  FROM vapma
  INTO TABLE itab_mat
  FOR ALL ENTRIES IN itab2
  WHERE vapma-matnr = itab2-matnr.   " More critereria may be needed

CHECK NOT itab_mat[] IS INITIAL.

SELECT vbak~vbeln vbak~vdatu vbap~kwmeng vbap~matnr vbep~mbdat
  FROM vbap
    INNER JOIN vbak ON vbak~vbeln = vbap~vbeln
    INNER JOIN vbep ON vbep~vbeln = vbap~vbeln
  INTO itab3
  FOR ALL ENTRIES IN itab_mat
  <b>WHERE vbap~vbeln = itab_mat-vbeln</b>.

I haven't checked this, so you will have to check it thoroughly.

There was a mistake in my WHERE. I've corrected it in <b>bold</b>.

Rob

Message was edited by: Rob Burbank

[Former Member]

I think you can try to get the data from VBAK/VBAP in one query and later use the particular internal table get the information from VBEP.

Select VBAK_{VBELN VBAK}VDATU VBAP_{KWMENG VBAP}MATNR from VBAP INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

into table ITAB3

for all entries in ITAB2

where VBAP~MATNR = ITAB2-MATNR.

SELECT VBEP_{VBELN VBEP}POSNR VBEP~MBDAT into itab4

from vbep

for all entries in itab3

where vbep~vbeln = itab3-vbeln

and vbep~posnr = itab3-posnr.

Regards

Anurag

OR

<b>Try and see if you can speed up using the VAPMA table</b>

Message was edited by: Anurag Bankley

Message was edited by: Anurag Bankley