Multiple filters with date(s)

[Former Member]

Has anyone had any experience using the OData $filter query option using multiple filters with a date range? Such as filters for sales orders for a customers with a sale date between two values?

Whenever I try this, the GW server throws an error about the filter having an incorrect value...I'm not sure if this is 100% supported yet with Gateway?

I can use the 'eq' operator and it seems to work most of the time, though sometimes I've found the order of the filter parameters makes a difference. Will this be polished up at all in SP04?

Cheers,

Mike

[former_member182048 · Accepted solution]

I have got multiple dates working in my services and i'll try and explain how you can do it in yours quite easily.

The example code below shows how to use the following filter string expression and parses it to a select option structure

/Orders?$filter=OrderDate ge datetime'1996-07-04T00:00:00' and OrderDate le datetime'1996-07-25T00:00:00'

What i noticed was when you use an expression with two operatons of the same property (OrderDate) the standard code fails to parse into the expected

IT_FILTER_SELECT_OPTIONS of /IWBEP/IF_MGW_APPL_SRV_RUNTIME~GET_ENTITYSET.

What i also noticed was that when this happens the expression string is passed in IV_FILTER_STRING, we can use this string to build the WHERE clauses using the same standard code.

The reason why the two dates or any two parameters with the same property dont get parsed into the select options:

/IWCOR/CL_ODATA_EXPR_UTILS=>GET_FILTER_SELECT_OPTIONS takes the expression you pass and parses it into a table of select options

The select option table returned is of type /IWCOR/IF_ODATA_TYPES=>EDM_SELECT_OPTION_T

which is a  HASHED TABLE  ..  WITH UNIQUE  KEY property .

And therefore cannot accept two entries with the same key, the code caters for this, you shouldn't use a compressed structure (the difference between AND and OR in a SELECT). This method is very useful, it brings back a tree of operation nodes, to get the results we want we just have to recursively call it.

Below is quick example of how you can take the IV_STRING and build your own SELECT OPTIONS

REPORT  yjptest01.
 DATA:
   lt_filter_select_options TYPE /iwbep/t_mgw_select_option,
   lt_select_option TYPE /iwcor/if_odata_types=>edm_select_option_t,
   ls_tree TYPE /iwcor/if_odata_types=>edm_node_s,
   lv_filter_str TYPE string,
   lv_value TYPE string,
   lv_timestamp TYPE timestamp,
   lv_date TYPE d.
 FIELD-SYMBOLS:
   <fs_select_option> LIKE LINE OF lt_select_option,
   <fs_select_options>  TYPE /iwcor/if_odata_types=>abap_select_option_s,
   <fs_range_tab> LIKE LINE OF lt_filter_select_options,
    <fs_range> TYPE /iwbep/s_cod_select_option,
   <fs_children> TYPE /iwcor/if_odata_types=>edm_node_t,
   <fs_node> TYPE /iwcor/if_odata_types=>edm_node_s.
 lv_filter_str = |OrderDate ge datetime'1996-07-04T00:00:00' and OrderDate le datetime'1996-07-25T00:00:00'|.
 ls_tree = /iwcor/cl_odata_expr_parser=>parse_filter_expression( iv_expression = lv_filter_str ).
 *--- the top node of tree should have two child nodes
 ASSIGN ls_tree-children->* TO <fs_children>.
 *--- use recursive function not nested loops
 LOOP AT <fs_children> ASSIGNING <fs_node>.
   lt_select_option = /iwcor/cl_odata_expr_utils=>get_filter_select_options( is_tree = <fs_node> )  .
   LOOP AT lt_select_option ASSIGNING <fs_select_option>.
     APPEND INITIAL LINE TO lt_filter_select_options ASSIGNING <fs_range_tab>.
     <fs_range_tab>-property = <fs_select_option>-property.
     LOOP AT <fs_select_option>-select_options ASSIGNING <fs_select_options>.
        APPEND INITIAL LINE TO <fs_range_tab>-select_options ASSIGNING <Fs_range>.
       <fs_range>-sign = <fs_select_options>-sign.
       <fs_range>-option = <fs_select_options>-option.
       lv_value = <fs_select_options>-low.
       /iwcor/cl_odata_type_helper=>parse_datetime( EXPORTING iv_datetime = lv_value IMPORTING ev_timestamp = lv_timestamp ).
       CONVERT TIME STAMP lv_timestamp TIME ZONE sy-zonlo INTO DATE lv_date.
       lv_value = lv_date.
       <fs_range>-low = lv_date.
       WRITE:/ 'Sign:    ', <fs_range>-sign.
       WRITE:/ 'Option:  ', <fs_range>-option.
       WRITE:/ 'Low:     ', <fs_range>-low, <fs_select_options>-low   .
     ENDLOOP.
   ENDLOOP.
 ENDLOOP.
* lt_filter_select_options should now have parsed values

** the code should use recursive functions and not nested loops, loops easier to show

Hope it helps.

For those interested a few additional lines of code to my copy of /iwcor/cl_odata_expr_utils and i was able to parse OData functions like startswith, endswith and substringof.

Cheers

John P

[Former Member]

Hi Mike, did you ever get an answer on this?  I am having the same problem with a date range (for flights) in BAPI_FLIGHT_GETLIST. 

Thanks, Mitchi

[Former Member]

Hi Mitchi,

The answer I got from SAP is it's not supported right now.

If you have two filters in the call to the service, they cannot be the same name...even if the filters wouldn't cancel themselves out.

I think it's case of following the OData spec a little too close without realizing the implications.

They said they would put it on the to-do list, but I'm not sure when it will be looked at.

If I hear anything I will be sure to post back here.

Cheers,

Mike