topic Re: Need help in select statement in Application Development and Automation Discussions

Need help in select statement

Former Member — Thu, 24 Nov 2005 11:34:33 GMT

hi all

i am stuck at one point i am selecting values from vbrk ainto one internal table and then putting loop on Internal table. and then i have to match the vbeln from bseg table with internal table value. but it taking long time more then 10 min. plz help i am attaching the code also..

SELECT vkorg kunag fkart vbeln erdat netwr FROM vbrk INTO TABLE t_itab

WHERE vkorg IN s_vkorg

AND kunag IN s_kunag

AND fkart IN s_fkart

AND erdat IN s_erdat.

CHECK sy-subrc EQ 0.

LOOP AT t_itab.

SELECT vbeln FROM bseg INTO table t_bseg

WHERE vbeln eq t_itab-vbeln

AND bschl EQ '01'

AND augbl EQ ''.

CHECK sy-subrc EQ 0.

loop at t_bseg.

MOVE: t_itab-kunag TO t_final-kunag,

t_itab-fkart TO t_final-fkart,

bseg-vbeln TO t_final-vbeln,

t_itab-erdat TO t_final-erdat,

t_itab-netwr TO t_final-netwr.

APPEND t_final.

*check sy-subrc eq 0.

SELECT SINGLE name1 FROM kna1 INTO t_final-name1

WHERE kunnr = t_final-kunag.

APPEND t_final.

i hope i am clear what i have to do...

i have to get data from vbrk and then i have to match vbrk-vbeln by bseg vbeln for exsisting document no.

plz help me

its urgent

thanks in advance

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 11:40:19 GMT

HI,

First check t_itab for vbeln..if sy-subrc ne 0..then delete that records.

endloop.

next iteration.

fill up directly from t_itab to t_final..here vblen available because now it has the values which were there in bseg.

Y do u need t_bseg?

Hope u r clear

Message was edited by: raja gurrala

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 11:40:44 GMT

use inner join on VBELN field

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 11:40:54 GMT

Hi Parul,

Change your code like this...

1.SELECT vkorg kunag fkart vbeln erdat netwr FROM vbrk INTO TABLE t_itab

WHERE vkorg IN s_vkorg

AND kunag IN s_kunag

AND fkart IN s_fkart

AND erdat IN s_erdat.

2. SELECT vbeln FROM bseg for all entries in t_itab

INTO table t_bseg

WHERE vbeln eq t_itab-vbeln

AND bschl EQ '01'

AND augbl EQ ''.

3. SELECT SINGLE name1 FROM kna1 for all entries in t_itab

INTO t_final-name1

WHERE kunnr = t_itab-kunag.

<b>Sorry, select the name1 values into another internal table...</b>

now sort these internal tables on the key fields, and then loop at t_itab, read the other two internal tables and populate your final internal table.

Regards,

Raj

Message was edited by: Rajasekhar Dinavahi

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 11:45:06 GMT

Hi parul,

can you access another tables insted of BSEG.

In the BSID, BSAD, BSIK, BSAK... there very similar information of bseg but divided by vendor, customer,....

it can help you.

Can you some filed like bukrs and gjahr to access before the bkpf and after the bseg?

bye

enzo

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 11:48:32 GMT

create the join of both table bseg and vbrk base d on condition and vbeln......

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 11:49:27 GMT

hi rajshekhar

for all entries is not workng the system is giving error sayng::field list with into clause is not allowed...

what should i do..

plz help

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 12:03:10 GMT

plz can u help in knowing me how to create join for this situation..

plz its urgent..

thanks in advance

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 12:08:26 GMT

Hi Parul,

Please avoid writing joins on BSEG...not advisable.

declare your t_bseg as

data: begin of t_bseg,

vbeln like bseg-vbeln,

end of t_bseg.

You can try the alternate way of accessing other tables like BSIK etc...but never write joins on BSEG.

Check this code....

******

REPORT Z_TESTRAJ

NO STANDARD PAGE HEADING

LINE-SIZE 200

MESSAGE-ID ZZ.

TYPES: BEGIN OF X_VBRK,

VKORG TYPE VKORG,

KUNAG TYPE KUNAG,

FKART TYPE FKART,

VBELN TYPE VBELN_VF,

ERDAT TYPE ERDAT,

NETWR TYPE NETWR,

END OF X_VBRK.

TYPES: BEGIN OF X_BSEG,

VBELN TYPE VBELN_VF,

END OF X_BSEG.

TYPES: BEGIN OF X_KNA1,

KUNNR TYPE KUNNR,

NAME1 TYPE NAME1,

END OF X_KNA1.

DATA: T_ITAB TYPE STANDARD TABLE OF X_VBRK WITH HEADER LINE,

T_BSEG TYPE STANDARD TABLE OF X_BSEG WITH HEADER LINE,

T_KNA1 TYPE STANDARD TABLE OF X_KNA1 WITH HEADER LINE.

START-OF-SELECTION.

SELECT VKORG KUNAG FKART VBELN ERDAT NETWR FROM VBRK INTO TABLE T_ITAB.

IF SY-SUBRC = 0.

SELECT VBELN INTO TABLE T_BSEG

FROM BSEG FOR ALL ENTRIES IN T_ITAB

WHERE VBELN EQ T_ITAB-VBELN

AND BSCHL EQ '01'

AND AUGBL EQ ''.

SELECT NAME1 INTO TABLE T_KNA1 FROM KNA1 FOR ALL ENTRIES IN T_ITAB

WHERE KUNNR = T_ITAB-KUNAG.

ENDIF.

******

Regards,

Raj

Message was edited by: Rajasekhar Dinavahi

Re: Need help in select statement

Former Member — Thu, 24 Nov 2005 12:09:30 GMT

add field vbeln like bseg vbeln in iternal table

SELECT vbrk_{vkorg vbrk}kunag vbrk_{fkart vbrk}erdat vbrk_{netwr bseg}vbeln FROM vbrk inner join bseg

on vbrk_{vbeln = bseg}vbeln

INTO TABLE t_itab

WHERE vbrk~vkorg IN s_vkorg

AND vbrk~kunag IN s_kunag

AND vbrk~fkart IN s_fkart

AND vbrk~erdat IN s_erdat.

AND bseg~bschl EQ '01'

AND bseg~augbl EQ ''.

CHECK sy-subrc EQ 0.