Query Problem

[Former Member]

Hi Experts

I am using the following query, when i give the input as date only(budat), it not responds, its executing for a long, what could be the problem.

select A~AUFNR

A~PLNBEZ

A~DISPO

B~KDAUF

B~KDPOS

C~BUDAT

C~WERKS

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM AFKO AS A

INNER JOIN AFPO AS B ON A_{AUFNR = B}AUFNR

INNER JOIN AFRU AS C ON A_{AUFNR = C}AUFNR

WHERE B~KDAUF IN ZSO

AND B~KDPOS IN ZLI

AND A~AUFNR IN ZAUF

AND C~BUDAT IN ZDAT

AND C~WERKS IN ZPL

AND A~PLNBEZ IN ZMAT.

Thanks in advance.

Regards

Rajaram

[Former Member]

Rajaram,

1.First thing is table joins will give the performance

issues. So split the selection query into 3 parts.

2. You are not using the primary keys in where

contions try to pass key values if possible.

Don't forget to reward if useful....

[Former Member]

how to change the query pls help me yar.

[Former Member]

Hi Raja,

Can u post your table declaration for itab as well as your selection screen?

[Former Member]

DATA : BEGIN OF itab OCCURS 0,

KDAUF like afpo-KDAUF, "SALE ORDER

KDPOS LIKE afpo-KDPOS, "LINE ITEM

AUFNR like afko-AUFNR, "Order

plnbez like AFKO-PLNBEZ, " Material Number

dispo like AFKO-DISPO, " MRO Controller

kunnr like VBAK-KUNNR, " Soldto party

dauat like AFPO-DAUAT, " Order type

rueck like AFRU-RUECK, " Confirmation No

rmzhl like AFRU-RMZHL, " Counter

aenam like AFRU-AENAM, " Changed by

budat like AFRU-BUDAT, " Posting date

werks like AFRU-WERKS, " Plant

ltxa1 like AFRU-LTXA1, " Confirmation text

ile01 like AFRU-ILE01, " Uom

ism01 like AFRU-ISM01, " Time taken

xmnga like AFRU-XMNGA, " Scrap qty

lmnga like AFRU-LMNGA, " yield

gmein like AFRU-GMEIN, " Base Unit of Measure

aueru like AFRU-AUERU, " Partial/Final Confirmation

vornr like AFRU-VORNR, " Activity Number

smeng like AFRU-SMENG, " PO Qty

wemng like AFPO-WEMNG, " Qty of goods received

rmnga like AFRU-RMNGA, " Rework qty

arbpl like CRHD-ARBPL, " Work centre

anzma like AFRU-ANZMA, " No. of machines used

ktext like CRTX-KTEXT, " Short description

END OF itab.

SELECTION-SCREEN BEGIN OF BLOCK bk1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: zso FOR vbak-vbeln.

SELECT-OPTIONS: zli FOR afpo-kdpos.

SELECT-OPTIONS: zmat FOR mara-matnr.

SELECT-OPTIONS: zwork FOR crhd-ARBPL.

SELECT-OPTIONS: zdat FOR afru-BUDAT.

SELECT-OPTIONS: zpl FOR afru-werks OBLIGATORY.

SELECT-OPTIONS: zmrp FOR afko-DISPO.

SELECT-OPTIONS: zauf FOR AFKO-AUFNR.

SELECT-OPTIONS: zedat FOR AFRU-ISDD.

SELECT-OPTIONS: zline FOR AFRU-GRUND.

SELECTION-SCREEN END OF BLOCK bk1.

[Former Member]

hi,

try this

select A~AUFNR

A~PLNBEZ

A~DISPO

B~KDAUF

B~KDPOS

C~BUDAT

C~WERKS

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM AFKO AS A

INNER JOIN AFPO AS B ON A_{AUFNR = B}AUFNR

INNER JOIN AFRU AS C ON A_{AUFNR = C}AUFNR.

[Former Member]

What about where classes.

i want to check some fiedls yar for example date.

[Former Member]

again its executing for a long yar. not working.

[Former Member]

Hi Raja,

Please try this,

SELECT a_{aufnr a}plnbez a_{dispo b}kdauf b_{kdpos c}budat c~werks

INTO CORRESPONDING FIELDS OF TABLE itab

FROM afko AS a

INNER JOIN afpo AS b ON a_{aufnr = b}aufnr

INNER JOIN afru AS c ON a_{aufnr = c}aufnr AND a_{rueck = c}rueck AND a_{rmzhl = c}rmzhl

WHERE b~kdauf IN zso

AND b~kdpos IN zli

AND a~aufnr IN zauf

AND c~budat IN zdat

AND c~werks IN zpl

AND a~plnbez IN zmat.

Key fields is very important in query for a better database performance.

[Former Member]

Rajaram,

Declare internal tables i_afko ,i_afpo,i_afru and i_final.

For fields in internal table see the selection statements.

IN i_final you have write all required fields from all there tables.

SELECT AUFNR PLNBEZ

INTO TABLE i_afko

WHERE AUFNR IN ZAUF AND

PLNBEZ IN ZMAT.

IF NOT i_afko IS INITIAL.

SELECT AUFNR KDAUF KDPOS

INTO TABLE i_afpo

FOR all entries in i_afko

where aufnr EQ i_afko-aufnr And

KDAUF IN ZSO AND

KDPOS IN ZLI.

ENDIF.

IF NOT i_afpo IS INITIAL.

SELECT AUFNR BUDAT WERKS

FROM AFRU

INTO TABLE i_AFRU

FOR ALL ENTRIES IN i_afpo

WHERE aufnr EQ i_afpo-aufnr AND

BUDAT IN ZDAT AND

WERKS IN ZPL.

ENDIF.

LOOP AT i_afko.

LOOP AT i_afpo where aufnr = i_afko-aufnr.

loop AT i_afru where aufnr = i_afko-aufnr..

MOVE requred values to i_final from 3 internal

table.

APPEND i_final.

ENDLOOP.

ENDLOOP.

ENDLOOP.

Don't forget to reward if useful.

[Former Member]

k thank very much