Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

help in the query

Former Member
0 Kudos
98

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

1 ACCEPTED SOLUTION

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos
71

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

8 REPLIES 8

former_member214131
Active Contributor
0 Kudos
71

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

0 Kudos
71

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.

0 Kudos
71

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

0 Kudos
71

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

0 Kudos
71

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
Developer Advocate
Developer Advocate
0 Kudos
72

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
0 Kudos
71

Hi,

You can use

Select - - - - -

from bsid into

table itab

where <cond>

and then

select - - - -

from BSAD

appending table itab

where <Cond>

Regards,

Gagan

0 Kudos
71

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