Selection query FOR ALL ENTRIES

[Former Member]

Hi Friends

This question is regarding FOR ALL ENTRIES in selection query. I have to write a query from a Z table which has millions of records. Selection keys are customers, periods, product families. All these keys are available as internal tables as follows,

IT_CUSTOMER

IT_PERIODS

IT_PRODUCT_FAMILIES

How can I select the Z table with all the entries in these 3 internal tables as keys?

Please guide me a optimized way for selection because it is going to have huge performance issue.

Thanks

Hari

[Former Member]

Hi Friends

This question is regarding FOR ALL ENTRIES in selection query. I have to write a query from a Z table which has millions of records. Selection keys are customers, periods, product families. All these keys are available as internal tables as follows,

IT_CUSTOMER

IT_PERIODS

IT_PRODUCT_FAMILIES

How can I select the Z table with all the entries in these 3 internal tables as keys?

Please guide me a optimized way for selection because it is going to have huge performance issue.

Thanks

Hari

[Former Member]

U cannot do for all enteries using three tables...Please follow the below logic to achieve your results.

U need to combine the enteries from the 3 internal tables into one say itab and use that in select query.

SELECT * from ztable

for all enteries in itab

where (conditions)

[Former Member]

Hi

write like this

if itab1[] is not initial.

select <field1> <field2>....from table ztable into corresponding fields of table itab for all entries in itab1 where

CUSTOMER = itab1-IT_CUSTOMER and

PERIODS = itab1-IT_PERIODS and

PRODUCT_FAMILIES = itab1-IT_PRODUCT_FAMILIES

endif.

if helpful

reward points

Regs

Manas Ranjan Panda

[Clemenss]

I think you'd be better off using ranges in the WHERE clause.

something like

<pre>

DATA:

lr_customers like range of IT_CUSTOMER-customer,

...

SELECT ... FROM Z...

where KUNNR in lr_customers

and period in lr_periods

...

</pre>

One of the reasons to use FOR ALL ENTRIES is that the WHERE clause can not exceed 32k Bytes for Oracle databases and this could happen if you put too many entries into a range.

If this is the case, use FOR ALLENTRIES IN with, say I_KUNNR and put the others into ranges.

Here's a nice routine to fill a range:

<pre>

• to be used:

• LOOP at i_cutomers

• PERFORM append_range

• USING 'IEQ' I_customers-customer ''

• CHANGING lr_customers.

• ENDLOOP.

&----

---

*& Form append_range

&----

---

• append selection range

----

---

FORM append_range USING p_signopt TYPE c

p_low TYPE any

p_high TYPE any

CHANGING pt_range TYPE table.

FIELD-SYMBOLS:

<range> TYPE ANY,

<sign> TYPE ANY,

<option> TYPE ANY,

<low> TYPE ANY,

<high> TYPE ANY.

DATA:

l_ref TYPE REF TO data.

CREATE DATA l_ref LIKE LINE OF pt_range.

ASSIGN l_ref->* TO <range>.

CHECK sy-subrc = 0.

ASSIGN COMPONENT 'SIGN' OF STRUCTURE <range> TO <sign>.

CHECK sy-subrc = 0.

ASSIGN COMPONENT 'OPTION' OF STRUCTURE <range> TO <option>.

CHECK sy-subrc = 0.

ASSIGN COMPONENT 'LOW' OF STRUCTURE <range> TO <low>.

CHECK sy-subrc = 0.

ASSIGN COMPONENT 'HIGH' OF STRUCTURE <range> TO <high>.

CHECK sy-subrc = 0.

<sign> = p_signopt(1).

<option> = p_signopt+1(2).

<low> = p_low.

<high> = p_high.

APPEND <range> TO pt_range.

ENDFORM. " append_range

</pre>

Regards,

Clemens