topic Re: Select Statement. in Application Development and Automation Discussions

Select Statement.

Former Member — Tue, 05 Sep 2006 19:31:22 GMT

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.

Re: Select Statement.

Former Member — Tue, 05 Sep 2006 19:41:26 GMT

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 table 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 corresponding fields of table ITAB3

for all entries in ITAB2

where VBAP~MATNR = ITAB2-MATNR.

*append itab3.

Hope this will help you.

Regards,

Vivek

PS: Award points if helpful

Re: Select Statement.

Former Member — Tue, 05 Sep 2006 19:42:36 GMT

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

Re: Select Statement.

Former Member — Thu, 07 Sep 2006 18:06:23 GMT

Hi Anurag,

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

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

Require ur help.

Regards,

Kamlesh

Re: Select Statement.

Former Member — Thu, 07 Sep 2006 18:17:51 GMT

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

Re: Select Statement.

Former Member — Thu, 07 Sep 2006 18:27:43 GMT

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

Rob

Message was edited by: Rob Burbank

Re: Select Statement.

Former Member — Thu, 07 Sep 2006 19:43:03 GMT

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

Try and see if you can speed up using the VAPMA table

Message was edited by: Anurag Bankley