topic Re: Problem with select query in Application Development and Automation Discussions

Problem with select query

suresh_goud — Tue, 16 Oct 2007 13:05:28 GMT

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

Re: Problem with select query

Former Member — Wed, 17 Oct 2007 04:27:54 GMT

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

Re: Problem with select query

Former Member — Wed, 17 Oct 2007 05:23:32 GMT

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

Re: Problem with select query

Former Member — Wed, 17 Oct 2007 05:42:28 GMT

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.

Re: Problem with select query

Former Member — Wed, 17 Oct 2007 06:00:57 GMT

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