When we are using FM "FREE_SELECTIONS_WHERE_2_RANGE" getting exception-2 "incorrect_expression"

[former_member286929]

because in importing parameter where_clause internal table is having more than 1000 entries. If I explicitly delete some entries and keep only 500 records then its working fine.

Here are the combination of WHERE clause:

( Vendor AND

Materials 1 to 2000 AND

HAS_OPEN = X )

Could you please suggest me how do I proceed to do selection more than thousands records ?

I have already tried to split the records put but it did not work in my scenario.

[thanga_prakash]

Hello Nilesh,

Please paste your code here.

Regards,

TP

[former_member286929]

DATA: lo_shd_item_range           TYPE REF TO cl_wlf_shd_item_range.

*----------------------------------------------------------------------*

* Set Document Type Range Value

*----------------------------------------------------------------------*

   lwa_s_range-option = /accgo/if_cmn_constants=>gc_option_eq.

   lwa_s_range-sign   = /accgo/if_cmn_constants=>gc_sign_i.

   lwa_s_range-low    = if_wb2_con=>shd_doc_type-tc.

   lo_shd_item_range->add_range(

         iv_fieldname = 'TYPE'

         is_range     = lwa_s_range ).

*----------------------------------------------------------------------*

* Set Side Range Value

*----------------------------------------------------------------------*

   lwa_s_range-option = /accgo/if_cmn_constants=>gc_option_eq.

   lwa_s_range-sign   = /accgo/if_cmn_constants=>gc_sign_i.

   lwa_s_range-low    = im_v_side.

   lo_shd_item_range->add_range(

         iv_fieldname = 'SIDE'

         is_range     = lwa_s_range ).

*----------------------------------------------------------------------*

* Set Materials Range Value

*----------------------------------------------------------------------*

* NOTE im_material having 2267 entries

   LOOP AT im_t_materials ASSIGNING <lwa_s_range>.

     lo_shd_item_range->add_range(

           iv_fieldname = 'MATNR'

           is_range     = <lwa_s_range> ).

   ENDLOOP.

*----------------------------------------------------------------------*

* Set Vendor / Customer Range Value

*----------------------------------------------------------------------*

   IF im_t_document IS INITIAL.

     IF im_t_partners IS NOT INITIAL.

       IF im_v_side = if_wb2_con=>pr_side-sales.

         lv_fieldname = 'CUSTOMER'.

       ELSEIF im_v_side = if_wb2_con=>pr_side-purchasing.

         lv_fieldname = 'VENDOR'.

       ENDIF.

       LOOP AT im_t_partners ASSIGNING <lwa_s_range>.

         lo_shd_item_range->add_range(

               iv_fieldname = lv_fieldname

               is_range     = <lwa_s_range> ).

       ENDLOOP.

     ENDIF.

   ELSE.

     lo_shd_item_range->add_range_tab(

         iv_fieldname = 'DOCUMENT'

         it_range     = im_t_document ).

   ENDIF.

*----------------------------------------------------------------------*

* Set Has Open Quantity

*----------------------------------------------------------------------*

   lwa_s_range-option = /accgo/if_cmn_constants=>gc_option_eq.

   lwa_s_range-sign   = /accgo/if_cmn_constants=>gc_sign_i.

   lwa_s_range-low    = abap_true.

   lo_shd_item_range->add_range(

         iv_fieldname = 'HAS_OPEN_QUAN'

         is_range     = lwa_s_range ).

*----------------------------------------------------------------------*

* Get WHERE CLAUSE

*----------------------------------------------------------------------*

   lo_shd_item_range->get_where_clauses( IMPORTING et_where_clauses = lt_where_clauses ).

*Do security check on where clause

   CLEAR lt_field_ranges.

   CALL FUNCTION 'FREE_SELECTIONS_WHERE_2_RANGE'  * Here system return sy-subrc =2

     EXPORTING

       where_clauses            = lt_where_clauses

     IMPORTING

       field_ranges             = lt_field_ranges

     EXCEPTIONS

       expression_not_supported = 1

       incorrect_expression     = 2

       OTHERS                   = 3.

   IF sy-subrc <> 0.

     RETURN.

   ENDIF.

   CLEAR lt_where_clauses.

   CALL FUNCTION 'FREE_SELECTIONS_RANGE_2_WHERE'

     EXPORTING

       field_ranges  = lt_field_ranges

     IMPORTING

       where_clauses = lt_where_clauses.

[raymond_giuseppi]

What would you do with this range of selection? Even if you build the where clause yourself, most SQL databases interpreter will be interrupted because of the length of the statement.

Regards,
Raymond

[former_member286929]

Below that code, dynamic select statement has been written.

Read table where_clause with index 1.

*         select the key

             SELECT type document item side sub_item INTO TABLE ex_t_shd_item

             FROM (<lwa_s_where_clauses>-tablename)  WHERE (<lwa_s_where_clauses>-where_tab).

Can Basis people handle this long length where clause statements?

[raymond_giuseppi]

First perform some tests with a transaction as SE16N or SE16 and paste some thousands of reference.

AFAIK this limitation is in the database, not in configuration of SAP system (in fact Abap try to call the DB with a too big statement and that triggers a dump) There may be some patch available depending on database type and version, but not often. (Look for OSS notes and on site of database provider)

Tips workaround:

• Mixing the original selection (the one which returned those material reference) with this one using JOIN or SUBQUERY
• Converting such a parameter in a FOR ALL ENTRIES clause
• Build subset of values into partial range and use SELECT APPENDING TABLE

Regards,

Raymond