topic Re: slect statement in Application Development and Automation Discussions

slect statement

Former Member — Thu, 03 Jan 2008 07:13:35 GMT

Hi,

i need to pick the data form 2 tables ( BSEG and BKPF) from BSEG - KUNNR,GSBER, BUKRS and from BKPF- BUDAT , BLDAT.

How to pick the data and on which field basis i need to pick the data

Re: slect statement

kesavadas_thekkillath — Thu, 03 Jan 2008 07:16:41 GMT

what r u r inputs...

the link beteween these 2 tables are belnr,mjahr,bukrs

first go to header-bkpf and then bseg

Re: slect statement

arpit_shah — Thu, 03 Jan 2008 07:20:56 GMT

BSEG is a pooled table so inner join is not possible so try this.

i m giving one example,

change as per your requirement,

select belnr gjahr budat

into corresponding fields of table t_head_expense

from bkpf

where budat in posdate

select belnr buzei gjahr hkont dmbtr shkzg sgtxt

into corresponding fields of table t_expense

from bseg

for all entries in t_head_expense

where belnr = t_head_expense-belnr and gjahr = t_head_expense-gjahr.

Regards,

Arpit

Re: slect statement

Former Member — Thu, 03 Jan 2008 07:21:21 GMT

for me inputs is bseg- kunnr, bseg- bukrs,bkpf-dudat

Re: slect statement

Former Member — Thu, 03 Jan 2008 07:22:35 GMT

you can select from BKPF-BELNR,BUKRS and from

BSEG-BELNR,BUKRS.

and you can relate both tables based on these fields.

Re: slect statement

Former Member — Thu, 03 Jan 2008 07:25:04 GMT

first select the data from bkpf

use for all entries and retrieve the information form bseg based on bukrs, belnr and gjahr

reward point if it is useful

Madhavi

Re: slect statement

RaymondGiuseppi — Thu, 03 Jan 2008 07:28:46 GMT

You could link BKPF and BSEG via the primary keys of BKPF

BUKRS

BELNR

GJAHR

But BSEG is a cluster, so some "join" options are unavailable, and if you use some ways to bypass this, performances will be very poor as the database is unable to optimize selection on BSEG (in fact cluster RFBLG), Legacy of old good time, where disk space was costly) (selecting too much records from one table and using the result in a FOR ALL ENTRIES from the second table with the last criteria.)

For this purpose use the "secondary indexes" for customer account, BSID and BSAD (cleared or not-cleared post, so you have to use 2 select)

Then if some fields are missing you can select from BSEG and BKPF the missing fields (via SELECT FOR ALL ENTRIES, using the primary keys of these tables)

Regards

Re: slect statement

kesavadas_thekkillath — Thu, 03 Jan 2008 07:29:17 GMT

select bukrs,belnr,gjahr from bkpf

into it_bkpf where

budat = pa_budat "input

and bukrs = pa_bukrs.

select < your needed fields> into table itab from bseg for all entries in it_bkpf

where belnr = it_bkpf-belnr

and gjahr = it_bkpf-gjahr

and bukrs = it_bkpf-bukrs.

and kunnr = pa_kunnr "<--inputs

Re: slect statement

mahaboob_pathan — Thu, 03 Jan 2008 07:34:27 GMT

SELECT

BSEG~KUNNR

BSEG~GSBER

BSEG~BUKRS

BKPF~BUDAT

BKPF~BLDAT

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG FROM

BSEG INNER JOIN BSEG

ON BSEG_{KUNNR = BKPF}KUNNR

WHERE BSEG~KUNNR IN S_KUNNR.

IF NOT IT_BSEG[] IS INITIAL.

SORT BY IT_BSEG-KUNNR.

DELETE ADJACENT DUPLICATES FROM IT_BSEG[].

ENDIF.

Re: slect statement

Former Member — Thu, 03 Jan 2008 08:26:08 GMT

i write like this:

select belnr budat bldat

into corresponding fields of table It_BKPF

from bkpf

where budat in SO_BUDAT.

IF IT_BKPF IS NOT INITIAL.

select bukrs belnr shkzg gsber dmbtr sgtxt kunnr

into corresponding fields of table IT_BSEG

from bseg

for all entries in IT_BKPF

where belnr = IT_BKPF-belnr.

ENDIF.

i am not getiing output for posting date , document date , currency, document no