Query -suggest me

[Former Member]

Hi Experts

I am using the following query, my input as 'ZSO' only, but now the query is not working properly. what could be the reason. pls help me on this.

SELECT a~aufnr

a~plnbez

a~dispo

b~kdauf

b~kdpos

c~budat

c~werks

INTO CORRESPONDING FIELDS OF TABLE i_FINAL

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.

Thanks in advance.

regards

Rajaram

[Former Member]

Hi Experts

I am using the following query, my input as 'ZSO' only, but now the query is not working properly. what could be the reason. pls help me on this.

SELECT a~aufnr

a~plnbez

a~dispo

b~kdauf

b~kdpos

c~budat

c~werks

INTO CORRESPONDING FIELDS OF TABLE i_FINAL

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.

Thanks in advance.

regards

Rajaram

[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 i_FINAL

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.

reward if its useful

[Former Member]

i have to check the plant, date, sales order, material and production order also, so i need those condition also. what can i do.

Suggest me.

Regards

Rajaram

[Former Member]

hi,

did u delcare these fields in i_final.

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.

[Former Member]

Yes , i have declared, but its not working yar.

[Former Member]

Hi Raja,

What is the value for sy-subrc after the query?

You can try to break the SQL statement by first selecting data only from AFKO and AFPO. Then you can add the third table and its join condition (one at a time). This approach will help you identify the condition due to which the query is failing.

I assume there is matching data available in all the three tables (meaning that your where conditions are all satisfied).

Regards,

Shyam

[Former Member]

yea, i have done 3 queries for 3 tables separately, but when execute for input for date, it takes long time.

bcoz,

SELECT AUFNR

POSNR

KDAUF

DAUAT

KDPOS FROM AFPO

INTO CORRESPONDING FIELDS OF TABLE i_afpo

FOR all entries in i_afko

where aufnr EQ i_afko-aufnr And

KDAUF IN ZSO AND

KDPOS IN ZLI.

SELECT AUFNR

BUDAT

ISDD

WERKS

SMENG

ARBID

RUECK

RMZHL

VORNR

LTXA1

GRUND

STOKZ

STZHL

AENAM

AUERU

EXNAM

LMNGA FROM AFRU

INTO CORRESPONDING FIELDS OF TABLE i_AFRU

FOR ALL ENTRIES IN i_afpo

WHERE aufnr EQ i_afpo-aufnr AND

BUDAT IN ZDAT AND

WERKS IN ZPL.

input as 01.01.2007 to 01.03.2007

if the i_afpo has 5000 records, it takes too much time.

how can we solve this.