help in the query

[Former Member]

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

[Former Member]

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

[Former Member]

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

[RichHeilman]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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

[RichHeilman]

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

[Former Member]

Hi,

You can use

Select - - - - -

from bsid into

table itab

where <cond>

and then

select - - - -

from BSAD

appending table itab

where <Cond>

Regards,

Gagan

[Former Member]

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