topic Re: help in the query in Application Development and Automation Discussions

help in the query

Former Member — Wed, 20 Jul 2005 16:34:15 GMT

hi folks,

Help me to build the query here.

Tables BKPF - fields belnr, blart

BSEG - fields kunnr,wrbtr, zuonr

I need to get the data into a single internal table whose strucutre consists of fields kunnr, blart,wrbtr and zuonr

the common field between the two tables is belnr and the query should be run based on 'zuonr' field

i.e

select ......................... where bseg-zuonr = p_advnum(paramter field where I get the data)

Question how to establish relation between two tables in asingle query without the use of 'JOIN' statements because BSEG is a cluster table and i cannot do that.

Thanks in advance.

Santhosh

Re: help in the query

Former Member — Wed, 20 Jul 2005 16:38:13 GMT

Hello,

You cannot use an Inner join as BSEG is NOT a Transparent table.

Instead use Secondary Indexes ( BSID, BSAD....) or use LDB: BRF.

Hope this helps you.

Best Regards, Murugesh AS

Re: help in the query

RichHeilman — Wed, 20 Jul 2005 16:41:48 GMT

Here's one way, but that hit to BSEG may take a while if you don't have an index for that field.



data: xbkpf type bkpf.
data: ibseg type table of bkpf with header line.


data: begin of itab occurs 0,
      kunnr type bseg-kunnr,
      blart type bkpf-blart,
      wrbtr type bseg-wrbtr,
      zuonr type bseg-zuonr,
      end of itab.

parameters: p_advnum type bseg-zuonr.


select * into corresponding fields of table ibseg
         from bseg
             where zuonr = p_advnum.

loop at ibseg.

  move-corresponding ibseg to itab.

  clear xbkpf.
  select single * from bkpf into xbkpf
          where belnr = ibseg-belnr.

  itab-blart = ibseg-blart.
  append itab.

endloop.

Regards,

Rich Heilman

Re: help in the query

RichHeilman — Wed, 20 Jul 2005 16:46:14 GMT

Or as Murugesh has suggested........



report zrich_0003.

data: begin of itab occurs 0,
      kunnr type bsad-kunnr,
      blart type bsad-blart,
      wrbtr type bsad-wrbtr,
      zuonr type bsad-zuonr,
      end of itab.

parameters: p_advnum type bseg-zuonr.

select * into corresponding fields of table itab
     from bsad
             where zuonr = p_advnum.

Re: help in the query

Former Member — Wed, 20 Jul 2005 17:34:43 GMT

I cannot use the bsad because it delas with 'cleared items' and I need the 'open items' too. therefore I have to use BSEG and BKPF.

How can I use the index to track the record because it becomes a performance issue here?

thanks

Re: help in the query

Former Member — Wed, 20 Jul 2005 17:54:51 GMT

Hello,

It is not suggested to use BKPF - BSEG tables as this takes away performance. Instead use BSID, BSAD, BSIK, BSAK, BSIS - Secondary Indexs as per your reqmt.

Hope this helps you.

Regds, Murugesh AS

Re: help in the query

Former Member — Wed, 20 Jul 2005 18:50:48 GMT

ok then i will read the data from tables BSID and BSAD into two different internal tables and the code goes like this

select * into corresponding fields of table patopenitemsitab

from bsid where zuonr = p_advnum.

select * into corresponding fields of table patcloseditemsitab

from bsad where zuonr = p_advnum.

How can I merge these two data into a single internal table?

Thanks

Santhosh

Re: help in the query

Former Member — Wed, 20 Jul 2005 19:03:47 GMT

Hi,

You can use

Select - - - - -

from bsid into

table itab

where <cond>

and then

select - - - -

from BSAD

appending table itab

where <Cond>

Regards,

Gagan

Re: help in the query

Former Member — Wed, 20 Jul 2005 19:32:00 GMT

Yes -there's no need to go to BKPF or BSEG at all. All the fields you need are in BSID and BSAD including BLART and ZUONR. If you do need other fields that aren't in BSID or BSAD, use the company code, fiscal year and document nuber form these tables to retrieve data from BKPF and/or BSEG.

Rob