Program goes in Infinite loop

[Former Member]

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.

[Venkat_Sesha]

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

[RaymondGiuseppi]

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

[Former Member]

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.

[Former Member]

Hi

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.

[Former Member]

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

[Former Member]

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.?

[Former Member]

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.

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 .

[ThomasZloch]

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

[Former Member]

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..?

[RaymondGiuseppi]

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

[Former Member]

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..

[Former Member]

Hi dharmin

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

whats ur exactly requirments.....

[Former Member]

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

[Former Member]

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

[Former Member]

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