topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Thu, 22 Jun 2006 10:24:43 GMT

Dear Friend,

Please give me the code for the below requirement,

Read table RESB using index ‘M’ (Matnr, werks, xloek, and KZEAR). Internal table to create should capture fields RSNUM, RSPOS, RSART, MATNR, WERKS, LGORT, KZEAR, XLOEK, XWAOK, SHKZG, BDMNG, and ENMNG).

I hard to find , how to use the index and the fields given in paraenthesis (matnr,werks,xloek and kzear) what it means?

thanks

Re: select query

dani_mn — Thu, 22 Jun 2006 10:29:13 GMT

Hi you don't have to mention index in your query system will select the best indexes according to query.

declare a internal table with the same fields you want to pick and write.

SELECT rsnum rspos

rsart matnr

....

INTO TABLE itab

WHERE matnr in matr AND

...

Regards,

Wasim Ahmed

Re: select query

Former Member — Thu, 22 Jun 2006 10:29:53 GMT

refer...

<b>http://help.sap.com/saphelp_47x200/helpdata/en/cf/21eb2d446011d189700000e8322d00/content.htm</b>You can use %hints for this..

Consider following example..

SELECT * FROM RESB

WHERE MATNR = '200-100' AND WERKS = '1100'.

Two indexes are defined on RESB: the primary index with the ID '0' via the fields (MANDT,RSNUM,RSPOS,RSART) and a secondary index with the ID 'M' via (MANDT,MATNR,WERKS,XLOEK,KZEAR,BDTER). This M index can be used for the above statement.

A more direct way to access a table via an index is by using a hint and by letting the optimizer decide which index should be selected.

SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'%_HINTS ORACLE 'INDEX("&TABLE&")'.

After all, the M index should be predefined explicitly. Since the precise naming of the M index is not known, all alternatives are simply listed. The Oracle optimizer ignores the index names which do not exist. In this case, the &TABLE& substitution cannot be used for the index names since these names do not depend on a table alias in the FROM condition:

SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'%_HINTS ORACLE 'INDEX("&TABLE&" "RESB~M" "RESB^M")'.

Re: select query

Former Member — Thu, 22 Jun 2006 10:30:29 GMT

I think that it is like this

data : begin of itab occurs 0,

rsnum type rsnum

......

end of itab.

select-options : s_matnr for mara-matnr.

select * from RESB

into corresponding fields of ITAB

from matnr = s_matnr

werks = s_werks

xloek = s_xloek

kzear = s_kzear.

You have to create the internal table containing the fields (RSNUM, RSPOS, RSART, MATNR, WERKS, LGORT, KZEAR, XLOEK, XWAOK, SHKZG, BDMNG, and ENMNG).

Message was edited by: mukesh kumar