Problem with select query

[suresh_goud]

Hi Experts,

I have a problem with below select query where it is excluding lower orders of collective order, where these orders are released but not confirmed.

I got a requirement where I have to show the lower orders along with header order (collective orders ) in report which are released but not confirmed.

Please help me ....... rewarded....

SELECT a_{auart a}werks a_{kdauf a}kdpos

b_{ftrmi b}aufnr b_{stlbez b}aufpl

b_{lead_aufnr b}prodnet " c_{rueck c}rmzhl

c_{vornr c}arbid c_{lmnga c}xmnga

c_{rmnga c}ism04 c_{ism05 c}budat

d~kunnr

e~arbpl

FROM aufk AS a

INNER JOIN afko AS b

ON b_{aufnr EQ a}aufnr

INNER JOIN afru AS c

ON c_{aufnr EQ a}aufnr

LEFT JOIN vbak AS d

ON d_{vbeln EQ a}kdauf

INNER JOIN crhd AS e

ON e_{objid EQ c}arbid AND e_{werks EQ a}werks

INTO CORRESPONDING FIELDS OF TABLE i_data

WHERE a~auart IN s_auart

AND a~kdauf IN s_vbeln

AND a~werks EQ p_werks

AND b~aufnr IN s_aufnr

AND b~ftrmi IN s_ftrmi

AND c~stokz EQ ''

AND c~stzhl EQ '00000000'.

Regards,

Suresh goud

[Former Member]

HI

use INNER JOIN not LEFT JOIN

and one more thing if you use this joins in ur report that will make a performance issue

if you use joins in ur program then the join for that tables will exist up to the program execution result in load on data base

so all ways better to use FOR ALL ENTRIES

reward if usefull

[Former Member]

hi,

use dis code:-

SELECT a_{auart a}werks a_{kdauf a}kdpos

b_{ftrmi b}aufnr b_{stlbez b}aufpl

b_{lead_aufnr b}prodnet " c_{rueck c}rmzhl

c_{vornr c}arbid c_{lmnga c}xmnga

c_{rmnga c}ism04 c_{ism05 c}budat

d~kunnr

e~arbpl

FROM aufk AS a

INNER JOIN afko AS b

ON b_{aufnr EQ a}aufnr

INNER JOIN afru AS c

ON c_{aufnr EQ a}aufnr

INNER JOIN vbak AS d

ON d_{vbeln EQ a}kdauf

INNER JOIN crhd AS e

ON e_{objid EQ c}arbid AND e_{werks EQ a}werks

INTO CORRESPONDING FIELDS OF TABLE i_data

WHERE a~auart IN s_auart

AND a~kdauf IN s_vbeln

AND a~werks EQ p_werks

AND b~aufnr IN s_aufnr

AND b~ftrmi IN s_ftrmi

AND c~stokz EQ ''

AND c~stzhl EQ '00000000'.

Dis will solve ur problem,if u find it useful,plzzz do rewards too.

regads,

ric.s

[Former Member]

it is better to use into table ....itab.

dont go for joins if u r joining four tables .......

it is better to for all entries.

SELECT a_{auart a}werks a_{kdauf a}kdpos

b_{ftrmi b}aufnr b_{stlbez b}aufpl

b_{lead_aufnr b}prodnet

FROM aufk AS a

INNER JOIN afko AS b

ON b_{aufnr EQ a}aufnr

INTO TABLE i_data1

WHERE a~auart IN s_auart

AND a~kdauf IN s_vbeln

AND a~werks EQ p_werks

AND b~aufnr IN s_aufnr

AND b~ftrmi IN s_ftrmi.

if i_data1[] is not initial .

if any sorting criteria is required based on primary keys write the sort statement here.

select c_{rueck c}rmzhl

c_{vornr c}arbid c_{lmnga c}xmnga

c_{rmnga c}ism04 c_{ism05 c}budat

d~kunnr

e~arbpl

FROM afru AS c inner join vbak AS d

ON c_{aufnr EQ a}aufnr innerjoin crhd AS e

like this u need to do...........................

u have joined around five tables ...definetly that wont work

endif.

[Former Member]

hi,

actually suguna is right if use 5 tables in inner joins den it will reduce its efficiency and i had by mistaken given u poor code. appologies for dat,

regrads,

ric.s