topic Re: Program goes in Infinite loop in Application Development and Automation Discussions

Program goes in Infinite loop

Former Member — Fri, 23 Nov 2012 08:52:20 GMT

HI all,

I have a requirement in which i need to fetch data from various tables. The relation is for 3 fields from bkpf i need to fetch corresponding data from bsid and bsad tables and from these need to fetch data from kna1 and knvv tables.

i tried d select querry with bkpf inner joining bsid/bsad then bsid inner joining kna1 (on field kunnr) and kna1 inner joining knvv (for field kunnr). But on execution it goes in infinite loop. Can u guys pls help me out with this.

This is the table created and the select querry:

TYPES: BEGIN OF ty_open_items,

bukrs type BKPF-BUKRS,

gjahr type BKPF-GJAHR,

monat type BKPF-MONAT,

belnr type BKPF-BELNR,

blart TYPE BSID-BLART,

bldat TYPE BSID-BLDAT,

* netdt TYPE BSID-NETDT,

augdt TYPE BSID-AUGDT,

wrbtr TYPE BSID-WRBTR,

hwaer type BKPF-HWAER,

augbl type BSID-AUGBL,

ktokd type KNA1-KTOKD,

kdgrp type KNVV-KDGRP,

zfbdt type BSID-ZFBDT,

zterm type BSID-ZTERM,

end of ty_open_items.

SELECT bkpf~bukrs bkpf~gjahr bkpf~monat bkpf~belnr bsid~blart bsid~bldat bsid~augdt bsid~wrbtr bkpf~hwaer bsid~augbl kna1~ktokd knvv~kdgrp bsid~zfbdt bsid~zterm

from ( bkpf INNER JOIN bsid on bkpf~bukrs = bsid~bukrs

INNER JOIN kna1 on bsid~kunnr = kna1~kunnr

INNER JOIN knvv on kna1~kunnr = knvv~kunnr )

into TABLE int_open_items

where bkpf~bukrs IN s_bukrs

and bkpf~gjahr IN s_gjahr

and bkpf~monat IN s_monat.

Re: Program goes in Infinite loop

Venkat_Sesha — Fri, 23 Nov 2012 09:03:31 GMT

You are joining three tables. Only thing I doubt is how can we play joining a Accounting Tables.

Better you can write the Select For All Entries.

or Use Cursor in the Selects. So that you may skip time out errors. Hope this helps

Re: Program goes in Infinite loop

RaymondGiuseppi — Fri, 23 Nov 2012 09:07:02 GMT

bkpf INNER JOIN bsid on bkpf~bukrs = bsid~bukrs

I'm sure you can give a better join criteria (hint: look for primary keys of BSEG) as you are joining every records of document header of on society with every records of customer records, not an infinite loop at all, but with some millions records per company in both tables that would give a very very very big chunk of data (millions of millions of records...)

Regards,

Raymond

Re: Program goes in Infinite loop

Former Member — Fri, 23 Nov 2012 09:28:11 GMT

Hi,

You got to avoid working with inner joins to help performance. But if you are forced to make sure you join on primary key fields and reduce the number of joins using for all entries on few tables.

Here in particular you havent mentioned the AS keyword in your select to declare the reference variable for your table.

SELECT bkpf~bukrs bkpf~gjahr bkpf~monat bkpf~belnr bkpf~hwaer

bsid~blart bsid~bldat bsid~augdt bsid~wrbtr bsid~augbl bsid~zfbdt bsid~zterm

kna1~ktokd

knvv~kdgrp

from bkpf AS bkpf

INNER JOIN bsid AS bsid

on bkpf~bukrs = bsid~bukrs

...................

Regards,

Vidya.

Re: Program goes in Infinite loop

Former Member — Fri, 23 Nov 2012 10:08:42 GMT

Hi Dharmin,

this is helpfull solution for you ..

whenever we want fetch the data from multiple table then we should always use to FOR ALL ENTRIES

BECAZ we can minimize the server load otherwise server load can be more if we wil use the inner join . innerjoin use only for two table .

This helpfull code for you ...

select f1 f2 f3 from bkpf into table it_bkpf where bukrs = p_bukrs .

if it_bkpf [] is not initial .

select fa1 fa2 fa3 from bsid into table it_bsid

FOR ALL ENTRIES IN BKPF

WHERE bukrs = it_bsid-bukrs and kunnr = it_bsid-kunnr .

endif.

if it_bsid[] is not initial .

select fb1 fb2 fb3 from bsad into table it_bsad

FOR ALL ENTRIES IN IT_BSID

WHERE bukrs = it_bsad-bukrs and kunnr = it_bsad .

endif.

if it_bsad[] is not initial .

select fc1 fc2 fc3 from kna1 into table it_kna1

FOR ALL ENTRIES IN IT_BSAD

where kunnr = it_kna1-kunnr .

endif.

Above I written the code .. I not given the field name you can make field name regarding ur need

i defined it_bkpf , it_bsid , it_bsad , it_kna1 its internal table type . when u wil write the code plz firstly declaration itab and workarea . this code is take minimum time for executing compare to inner join . that's y FOR ALL ENTRIE ALWAYS BETTER FOR FETCHING THE DATA FROM MULTIPLE TABLE .

<<< Do not ask for points ... Read the forum rules >>>

Thanks Regards

Suraj singh

Message was edited by: Kesavadas Thekkillath

Re: Program goes in Infinite loop

Former Member — Fri, 23 Nov 2012 10:32:16 GMT

I used AS key but its still not helping. And what other method can i use instead of join? Can you give an example .There are four tables from which i need to fetch data.

Re: Program goes in Infinite loop

Former Member — Fri, 23 Nov 2012 10:40:17 GMT

HI suraj,

But then how can i get all the data in one list. i.e i need to get all the data in the structure which i declared ty_open_items.?

Re: Program goes in Infinite loop

Former Member — Fri, 23 Nov 2012 11:13:11 GMT

Hi Dharmin , firstly u declare type groups for each table field according to ur need .

after that u make one final itab with declare the types group ty_open_items according to the whatever field define all the types groupds for all table field . where we can store all data from diffrent tables .

then

after select query u have to use the loop for store the all type groups field into ty_open_item by final itab .

some code for help full .....

types group for first table field

types: begin of ty_bkpf,

f1 type bkpf-f1,

f2 type bkpf-f2,

end of ty_bkpf.

.....

....

.....

n ...types group

as like u have to declare types group for all table field . after declaraion of all type groups then u have to declare one ty_open_items type groups and nd get the field from above define types group ..

nd declare the final itab .

in which all data wil be stroe

as like

data: it_final type ty_open_items .

data: wa_final like line of it_final .

after fetching the data by using for all entries

loop it_bkpf into wa_bkpf

loop it_bsid into wa_bsid where bukrs = wa_bkpf-bukrs.

loop it_bsad into wa_bsad where bukrs = wa_bsid-bukrs and kunnr = wa_bsid-kunnr .

....

...

wa_final-bukrs = wa_bkpf-bukrs.

wa_final-kunnr = wa_bsid-kunnr .

.........

.......

endloop.

loop it_final into wa_final

here u can use write statement for display the data on selection screen .

endloop .

all the data will store in ty_open_items .and display the only single list display .

Re: Program goes in Infinite loop

ThomasZloch — Fri, 23 Nov 2012 12:33:11 GMT

Please ignore all "avoid joins, use FAE" type of misleading advice. It's an uphill struggle against huge windmills that we will never win.

As Raymond indicated, make sure you construct your joins properly, most importantly with ON conditions that make full use of primary or secondary indexes, otherwise your result set and/or response time will explode.

Also the WHERE-conditions applied to the whole lot are important, same thing with using qualified indexes whenever possible.

BKPF and BSID must be joined using BUKRS, BELNR and GJAHR. You might not need BKPF at all, at quick glance all information you require is also in BSID (edit: except HWAER...). For BSID and KNA1, KUNNR is sufficient. KNVV has VKORG, VTWEG and SPART as additional key fields, I don't see a way to join this properly, as BSID does not offer these. Do you really need KDGRP?

Thomas

Re: Program goes in Infinite loop

Former Member — Sat, 24 Nov 2012 19:33:04 GMT

well yes KDRGP is needed..nd so is HWAER.which brings BKPF in picture. For joining BKPF nd BSID i tried using BELNR but it still is the same.Even using KUNNR in joining condition vl increase no. of records..so how would it b done other then joins..?

Re: Program goes in Infinite loop

RaymondGiuseppi — Sat, 24 Nov 2012 19:44:04 GMT

If you don't fin it yourself, you MUST join BKPF and BSEG, BSID, BSIS or any similar table with the full primary key of the document header BKPF : so BUKRS and BELNR and GJAHR are required.

For KNVV it is more difficult and may depend on your customizing. You can find it via the actual invoice, VBRK information, where VBRK-VBELN = BKPF-AWKEY if BKPF-AWTYP = 'VBRK'. Else this link can not exist. e.g. a payment document may not have enough information to get this information, then ask functional to get more information.

Regards,

Raymond

Re: Program goes in Infinite loop

Former Member — Mon, 26 Nov 2012 11:09:50 GMT

Hi suraj,

I tried this but it still goes in dat infinite stage i.e keeps on loading..

is it due to the loop within loop within loop statements..? And i even need to map net due dates which i included in last loop statements..so there are four loop statements..

Re: Program goes in Infinite loop

Former Member — Mon, 26 Nov 2012 12:17:21 GMT

Dear Dharmin,

You may use this easiest and effective approach to fetch your required data.

as a part of your beginning work.

it's a different thing that you have loads of alternate and advance way to achieve

the result .. at your enough time you can workout on the same to enhance your code for performance.

here it is.

1) Create internal table from all required fields.

2) join only tables bkpf,bsid,bsad in nternal table ( use into corresponding).

3) loop at internal table.....

using where <kna1-field> = internal table field.

using where <knvv-field> = internal table field.

modify internal table

Thanks & Regards,

Avirat

Re: Program goes in Infinite loop

Former Member — Mon, 03 Dec 2012 06:39:33 GMT

Hi dharmin

Can u explain , how many table and field you are using for fetch the data .

whats ur exactly requirments.....

Re: Program goes in Infinite loop

Former Member — Mon, 10 Dec 2012 13:46:20 GMT

hi suraj,

i m using four tables bkpf,bsid,kna1 and knvv to fetch data. Now i need to join this four tables which i guess would slow down d code. so i needed help on how to join them using For all entries concept

Re: Program goes in Infinite loop

Former Member — Tue, 18 Dec 2012 04:48:17 GMT

Hi Dharmin,

I hope this solution helpful for you ...

select KUNNR LAND1 NAME1 from kna1 where kna1 = p_kna1 .

if it_kna1 is not initial [].

select KUNNR BUKRS BEGRU from knb1 for all entries IN IT_Kna1

WHERE KUNNR = IT_KNA1-KUNNR.

ENDIF.

IF IT_KNb1 IS NOT INITIAL [].

select BUKRS KUNNR GJAHR WAERS from bsid for all entries in it_knb1

where kunnr = it_knb1-kunnr .

endif.

if it_bsid is not initial [].

select BUKRS BELNR GJAHR from bkpf for all entries in it_bsid

where bukrs = it_bsid-bukrs .

by using above query you can featch the data from among the table .

if this is helpful solution for you then I shall be happy.

thanks and Regards

Suraj singh