can we join the transparent and pooled tables?

[Former Member]

hi friends,

i have a doubt that is when we want to get the data from transparent and pooled tables it is not possible to join the tables.

so should we go with nested select statements or is there any way to get the data? with better performance

if i go with nested select statements it takes a lot time thats y i need a better way

for example i want the data from BKPF and BSEG based on BELNR

please send me how can we get it

regards

jagadish

[Former Member]

hi,

BSEG is a cluster table but not a pooled table.

Regards,

Sailaja.

Message was edited by: Sailaja N.L.

[Former Member]

sorry

ok bseg is cluster table but for clusters also we cant write join

i tried thats the problem

so can u say any other way?

regards

jagadish

[Former Member]

Hi,

I believe you cannot join either cluster or pooled tables..

Use FOR ALL ENTRIES..

select * from bkpf

into table t_bkpf

where .....

select * from bseg

into table t_bseg

for all entries in t_bkpf

where belnr = t_bkpf-belnr

and gjahr = t_bkpf-gjahr

and bukrs = t_bkpf-bukrs.

Thanks,

naren

Message was edited by: Narendran Muthukumaran

[Former Member]

even though it takes a lot time

and the run time is exeeding

now iam running this in the background

but i want to know other solution for like these problems

regards

jagadish

[former_member181962]

YOu have to use the secondary index tables ike BSAK, BSIK, BSIS, BSAD, BSID etc instead of BSEG.

[Former Member]

Hi,

Please see earlier reply regarding FOR ALL ENTRIES..

Thanks,

Naren

[Former Member]

Could you please post the select statements?

Rob

[Former Member]

hi rob

thanks for response

see the below code once

SELECT * FROM bkpf

WHERE gjahr = p_gjahr

AND ( monat BETWEEN lv_1st_mth AND gw_prev_monat ).

*- Selection with cost center

IF gw_kostl NE SPACE.

s_kostl = s_kostl+3(10).

SELECT * FROM bseg

WHERE bukrs = bkpf-bukrs

AND belnr = bkpf-belnr

AND gjahr = bkpf-gjahr

AND kokrs = p_kokrs

AND kostl IN s_kostl

AND buzei = '001'

AND lstar <> ' '.

MOVE bseg-lstar TO itab2-lstar .

MOVE bkpf-bukrs TO itab2-bukrs.

MOVE bkpf-belnr TO itab2-belnr.

MOVE bkpf-gjahr TO itab2-gjahr.

MOVE bkpf-monat TO itab2-monat.

MOVE bkpf-budat TO itab2-budat.

MOVE bseg-kokrs TO itab2-kokrs.

MOVE bseg-buzei TO itab2-buzei.

• MOVE bseg-wrbtr TO itab2-wrbtr.

*Changed----

---

IF bseg-shkzg = 'H'.

lv_wrbtrcd = 0 - bseg-wrbtr.

itab2-wrbtr = lv_wrbtrcd.

ELSEIF bseg-shkzg = 'S'.

MOVE bseg-wrbtr TO itab2-wrbtr.

ENDIF.

*----

---

MOVE bseg-fdwbt TO itab2-fdwbt.

MOVE bseg-sgtxt TO itab2-sgtxt.

MOVE bseg-kostl TO itab2-kostl.

APPEND itab2.

ENDSELECT.

ELSE.

s_kostl = p_estat+0(4).

SELECT * FROM bseg

WHERE bukrs = bkpf-bukrs

AND belnr = bkpf-belnr

AND gjahr = bkpf-gjahr

AND kokrs = p_kokrs

AND kostl IN s_kostl

AND buzei = '001'

AND lstar <> ' '.

IF bseg-kostl+0(4) = s_kostl.

MOVE bseg-lstar TO itab2-lstar.

MOVE bkpf-bukrs TO itab2-bukrs.

MOVE bkpf-belnr TO itab2-belnr.

MOVE bkpf-gjahr TO itab2-gjahr.

MOVE bkpf-monat TO itab2-monat.

MOVE bkpf-budat TO itab2-budat.

MOVE bseg-kokrs TO itab2-kokrs.

MOVE bseg-buzei TO itab2-buzei.

• MOVE bseg-wrbtr TO itab2-wrbtr.

*Changed----

---

IF bseg-shkzg = 'H'.

lv_wrbtrcd = 0 - bseg-wrbtr.

itab2-wrbtr = lv_wrbtrcd.

ELSEIF bseg-shkzg = 'S'.

MOVE bseg-wrbtr TO itab2-wrbtr.

ENDIF.

*----

---

MOVE bseg-fdwbt TO itab2-fdwbt.

MOVE bseg-sgtxt TO itab2-sgtxt.

MOVE bseg-kostl TO itab2-kostl.

APPEND itab2.

ENDIF.

ENDSELECT.

ENDIF.

ENDSELECT.

regards

jagadish

[Former Member]

i tried as below also

please check it

select * from bkpf

into table t_bkpf

where .....

select * from bseg

into table t_bseg

for all entries in t_bkpf

where belnr = t_bkpf-belnr

and gjahr = t_bkpf-gjahr

and bukrs = t_bkpf-bukrs.

regards

jagadish

[Former Member]

Well, the nested selects aren't the recommended way to do selects, but you problem is mainly in your select from BKPF. Without at least the company code, you'll be looking through the entire table sequentially.

If you can get the company code, parhaps you can change the select from fiscal period to document date. There is a secondary index on those two field in BKPF.

Rob

[Former Member]

thanks for responding

please can u give me a sample statement

regards

jagadish

[Former Member]

You can start with something like:

TABLES: bkpf, t001.

SELECT-OPTIONS: s_docdt FOR bkpf-bldat.

DATA: bkpf_int TYPE TABLE OF bkpf .
RANGES: r_bukrs FOR bkpf-bukrs.

r_bukrs-option = 'EQ'.
r_bukrs-sign   = 'I'.
SELECT bukrs FROM  t001
  INTO r_bukrs-low.
  APPEND r_bukrs.
ENDSELECT.

REFRESH bkpf_int.
SELECT        * FROM  bkpf
  INTO TABLE bkpf_int
  WHERE  bukrs IN r_bukrs
  AND    bldat IN s_docdt.

Rob

[Former Member]

Hi Jagadish,

I think the best option in your case would be to use to tables BSIK (Vendors), BSAK (Cleared Items

), BSID (Customers)and BSAD (Cleared Items) which are secondary index tables. These tables will have all the records in your BSEG tables and using these tables will improve performance of your data retrieval.

Cheers,

Vikram

Pls reward for helpful replies!!