topic Re: Index in data base table in Application Development and Automation Discussions

Index in data base table

Former Member — Fri, 11 Jan 2008 05:34:46 GMT

Hi all.

I have two table MAEX other Ztable.

MAEX have key field: MATNR, ALAND, and GEGRU.

ZTABLE have key field: Delivery, delivery item but MATNR is INDEX field.

I need to fetch ALNUM from MAEX. remaining fields i need to fetch from ZTABLE .Pls help me.shall I use Index as Key field?

To be reward all helpful answers.

Regards.

JNJ

Re: Index in data base table

Former Member — Fri, 11 Jan 2008 05:40:25 GMT

select <fields>

into table <itab>

from MAEX as a inner join Ztab as b

on a_{matnr = b}matnr

where <condition>

Madhavi

Re: Index in data base table

Former Member — Fri, 11 Jan 2008 05:41:00 GMT

Hi,

From performance point of view its always better to have key fields in the where clause of select statement and if you dont have any key field in where clause the second best option is to have a field which have index in the table.

regards,

Pankaj

Re: Index in data base table

Former Member — Fri, 11 Jan 2008 05:45:18 GMT

since matnr is your index field so matnr will not match directly to your standard table matnr.

so do one thing when you are fetching the record from z table then first fetch the record in internal table then convert the data type of your indexed matnr to your standard one, then match the record by read table and fill in final one/

regards

vijay

Re: Index in data base table

Former Member — Fri, 11 Jan 2008 05:45:27 GMT

Hi JNJ,

you can use select following statment :

select maex_{alnum ztable}field1 field2....

into table itab from maex

inner join ztable on maex_{matnr = ztable}matnr

where ...

Syed Tayab Shah

Re: Index in data base table

RoySayak — Fri, 11 Jan 2008 06:22:19 GMT

Hi,

It may be helpful to you...

TYPES: BEGIN OF ty_maex,

matnr TYPE maex-matnr,

alnum TYPE maex-alnum,

END OF ty_vbak.

TYPES: BEGIN OF ty_ztable,

matnr TYPE ztable-matnr,

delivery TYPE ztable-delivery,

delivery_item TYPE ztable-delivery_item,

END OF ty_vbap.

TYPES: BEGIN OF ty_final,

matnr TYPE maex-matnr,

alnum TYPE maex-alnum,

delivery TYPE ztable-delivery,

delivery_item TYPE ztable-delivery_item,

END OF ty_final.

WORK AREA DECLARATION--

DATA: wa_maex TYPE ty_maex,

wa_ztable TYPE ty_ztable,

wa_final TYPE ty_final.

INTERNAL TABLE DECLARATION FOR WORK AREA--

DATA: it_maex TYPE TABLE OF ty_maex,

it_ztable TYPE TABLE OF ty_ztable,

it_final TYPE TABLE OF ty_final.

SELECT matnr alnum

FROM maex INTO TABLE it_maex WHERE matnr in s_matnr(selection screen field).

SORT it_maex BY matnr.

IF it_maex[] IS NOT INITIAL.

SELECT matnr delivery delivery_item

FROM ztable INTO TABLE it_ztable

FOR ALL ENTRIES IN it_maex

WHERE matnr = it_maex-matnr.

ENDIF.

LOOP AT it_ztable INTO wa_ztable.

READ TABLE it_maex INTO wa_maex WITH KEY matnr = wa_ztable-matnr.

wa_final-matnr = wa_ztable-matnr.

wa_final-alnum = wa_maex-alnum.

wa_final-delivery = wa_ztable-delivery.

wa_final-delivery_item = wa_ztable-delivery_item.

APPEND wa_final TO it_final.

ENDLOOP.

Regards,

Sayak... ..."reward if it is helpful"