topic Re: Dynamic select-statement with ranges in Application Development and Automation Discussions

Dynamic select-statement with ranges

michael_fallenbchel — Wed, 30 Sep 2009 06:01:28 GMT

Good morning experts,

I just wanted to know if this is possible:

I had a report with a selection-screen where I can enter a table-name and one ore more filed-names (separated by ;).

After that, I create a table like my entered (for example MARA)

CREATE DATA gt_table TYPE TABLE OF (p_tab).
ASSIGN gt_table->* TO <gt_table>.

Then I split my entered fields (at and for each filed I made a selection (with function COMPLEX_SELECTIONS_DIALOG).

Result of this is for each field a range-tab which I write in a new table - first column is the name of the field, second column is a range-tab:

TYPES: BEGIN OF t_ranges,
          sign TYPE tkeppllevs-sign,
          opt  TYPE tkeppllevs-opt,
          low  TYPE rsdslow,
          high TYPE rsdslow,
        END OF t_ranges.

DATA: BEGIN OF gs_conditions,
        fieldname TYPE string,
        ranges    TYPE TABLE OF t_ranges.
DATA: END OF gs_conditions.
DATA: gt_conditions LIKE TABLE OF gs_conditions.

Now I want to make a select on the entered table (MARA).

So I make a loop on my gt_conditions:

LOOP AT gt_conditions INTO gs_conditions.
  l_index = sy-tabix.
 CONCATENATE 'gt_conditions[' l_index ']-ranges' INTO l_condition.
  CONDENSE l_condition NO-GAPS.
  CONCATENATE gs_conditions-fieldname 'IN' l_condition INTO lt_condition SEPARATED BY space.
  IF sy-tabix > 1.
    CONCATENATE 'AND' lt_condition INTO lt_condition SEPARATED BY space.
  ENDIF.
  APPEND lt_condition.
ENDLOOP.

If I look at it after the loop, it looks correct:

MATNR IN gt_conditions[1]-ranges

AND ERSDA IN gt_conditions[2]-ranges

But this wont work because of "gt_conditions[1]-ranges". I know, i can work with the "table in table", because gs_conditions-ranges works (I already tested this).

Has anybody any idea how to make my idea work?

Re: Dynamic select-statement with ranges

RaymondGiuseppi — Wed, 30 Sep 2009 06:42:18 GMT

Did you try to use FM like CONVERT_SELECT_INTO_WHERE, ADSPC_CREATE_WHERE_CLAUSE, DYNSQL_GENERATE_WHERE_CLAUSE or FREE_SELECTIONS_RANGE_2_WHERE

Regards,

Raymond

Re: Dynamic select-statement with ranges

venkat_o — Wed, 30 Sep 2009 06:58:39 GMT

Hi Michael, Your definition to range table is wrong i believe. To use IN operator for MATNR, you need to do the following way your declarations.


TYPES: BEGIN OF t_ranges,
          sign TYPE tkeppllevs-sign,
          opt  TYPE tkeppllevs-opt,
          low  TYPE rsdslow,
          high TYPE rsdslow,
        END OF t_ranges.
 
DATA: BEGIN OF gs_conditions,
        fieldname TYPE string,
        ranges    TYPE TABLE OF t_ranges.
DATA: END OF gs_conditions.
DATA: gt_conditions LIKE TABLE OF gs_conditions.

Change above code to below code


DATA: BEGIN OF gs_conditions,
        fieldname TYPE string,
        ranges    TYPE RANGE OF mara-matnr. "This is like defining SELECT-OPTIONS
DATA: END OF gs_conditions.
DATA: gt_conditions LIKE TABLE OF gs_conditions.

Thanks Venkat.O

Re: Dynamic select-statement with ranges

michael_fallenbchel — Wed, 30 Sep 2009 07:09:07 GMT

@Raymond: Thanks for your input, I will check those FMs, maybe the right is there...

@Venkat: No, you''re not right. I I will made my definition like you say, there only can be entered a range of MATNR. I want to save there a table of any range I enter.

Example: I enter fields MATNR and ERSDA in my selections screen:

Now I have to ranges, one for MATNR (maybe from 1 to 200 and 500 - 1000), and one for ERSDA (maybe 01.01.2000 to 31.12.2000 and NOT 01.06.2000), so my table with the conditions would look like this:

MATNR_____RANGE

ERSDA_____RANGE

If you open the ranges, the table would look like this:

MATNR____BT_001_200

__________BT_500_1000

ERSDA____BT_01012000_31122000

__________NT_01062000

Do you know what I mean? I I make al oop on the conditions-table into gs_conditions, the first row would look like this:

gs_conditions-fieldname = MATNR

gs_conditions-range:

BT_001_200

BT_500_1000

Re: Dynamic select-statement with ranges

hymavathi_oruganti — Wed, 30 Sep 2009 07:12:59 GMT

Hi ,

i did not understand as why are you declaring ranges like below:

TYPES: BEGIN OF t_ranges,

sign TYPE tkeppllevs-sign,

opt TYPE tkeppllevs-opt,

low TYPE rsdslow,

high TYPE rsdslow,

END OF t_ranges.

why cant you use ranges stamenet directly?

Ranges: t_ranges for MARA-MATNR (Example)

Now t_ranges behaves like select-options.

in your internal table, you can declare like below:

DATA: BEGIN OF gs_conditions,

fieldname TYPE string,

ranges TYPE TABLE OF t_ranges.

DATA: END OF gs_conditions.

now you can use gs_conditions-low and gs_conditions-high in your slect queries.

for example:

select * from <dbtab> into <itab>

where <fieldvalue> in gs_conditions-ranges.

select * from <dbtab> into <itab>

where <fieldvalue> = gs_conditions-low.

Re: Dynamic select-statement with ranges

michael_fallenbchel — Wed, 30 Sep 2009 07:18:29 GMT

I try to explain:

I don't want to use the ranges for a specific field - I want to use it for every field.

The user enters a table and the fields, I don't know what he enters...so I cant make the ranges like t_ranges for MARA-MATNR.

Also I can't make the select statement "static" with a specific field, I have to "build" the where-statement completely:

SELECT * FROM (p_tab)
   INTO TABLE <gt_table>
        WHERE (lt_condition).

Is it clearer now?

Re: Dynamic select-statement with ranges

hymavathi_oruganti — Wed, 30 Sep 2009 07:25:15 GMT

can you tell me whats the error you are getting when you are using the logic given by you?

Re: Dynamic select-statement with ranges

hymavathi_oruganti — Wed, 30 Sep 2009 07:29:03 GMT

tell me one thing,

LOOP AT gt_conditions INTO gs_conditions.

l_index = sy-tabix.

CONCATENATE 'gt_conditions[' l_index ']-ranges' INTO l_condition.

CONDENSE l_condition NO-GAPS.

You are inside the loop and you are sure that you are accessing first record of gt_conditions, then y to use index again?

why cant the below code work? try using wok area "gs_conditions" instead of gt_conditions.

LOOP AT gt_conditions INTO gs_conditions.

l_index = sy-tabix.

CONCATENATE 'gs_conditions-ranges' INTO l_condition.

CONDENSE l_condition NO-GAPS.

Re: Dynamic select-statement with ranges

michael_fallenbchel — Wed, 30 Sep 2009 07:38:19 GMT

@Hymavathi:

Error is the following:

An exception occurred that is explained in detail below.
The exception, which is assigned to class 'CX_SY_DYNAMIC_OSQL_SEMANTICS', was
 not caught and
therefore caused a runtime error.
The reason for the exception is:
The current ABAP program has tried to execute an Open SQL statement
which contains a WHERE, ON or HAVING condition with a dynamic part.
The part of the WHERE, ON or HAVING condition specified at runtime in
a field or an internal table, contains the invalid value
 "GT_CONDITIONS[1]-RANGES".

@Hymavathi, second time:

This is the whole code for the select:

LOOP AT gt_conditions INTO gs_conditions.
  l_index = sy-tabix.
  CONCATENATE 'gt_conditions[' l_index ']-ranges' INTO l_condition.
  CONDENSE l_condition NO-GAPS.

  CONCATENATE gs_conditions-fieldname 'IN' l_condition INTO lt_condition SEPARATED BY space.
  IF sy-tabix > 1.
    CONCATENATE 'AND' lt_condition INTO lt_condition SEPARATED BY space.
  ENDIF.
  APPEND lt_condition.
ENDLOOP.

SELECT * FROM (p_tab)
   INTO TABLE <gt_table>
        WHERE (lt_condition).

You can see, the select is after the loop. So gs_conditions-ranges wouldn't be right, then the where statement would only use the last range-tab from my table gt_conditions. (loop at...endloop => gs-conditions-range = last gt_conditions-range).

Re: Dynamic select-statement with ranges

michael_fallenbchel — Wed, 30 Sep 2009 08:12:20 GMT

I got it!! Thanks all for your help.

For whom is interested - this is it:

DATA: lt_field_ranges  TYPE rsds_trange.
DATA: ls_field_ranges  TYPE rsds_range.
DATA: ls_rsds_trange   TYPE rsds_frange.
DATA: lt_where_clauses TYPE rsds_twhere.
DATA: ls_where_clauses TYPE rsds_where.

(...)

ls_field_ranges-tablename = p_tab.

LOOP AT gt_conditions INTO gs_conditions.
  ls_rsds_trange-fieldname = gs_conditions-fieldname.
  ls_rsds_trange-selopt_t[] = gs_conditions-ranges[].
  APPEND ls_rsds_trange TO ls_field_ranges-frange_t.
ENDLOOP.
APPEND ls_field_ranges TO lt_field_ranges.

CALL FUNCTION 'FREE_SELECTIONS_RANGE_2_WHERE'
  EXPORTING
    field_ranges  = lt_field_ranges
  IMPORTING
    where_clauses = lt_where_clauses.


LOOP AT lt_where_clauses INTO ls_where_clauses.
  SELECT * FROM (p_tab)
     INTO TABLE <gt_table>
          WHERE (ls_where_clauses-where_tab).
ENDLOOP.

Thank you all for your help!

Re: Dynamic select-statement with ranges

hymavathi_oruganti — Wed, 30 Sep 2009 08:32:00 GMT

Hi Michael,

Try this one.

Field symbols: <fs> type any.

LOOP AT gt_conditions INTO gs_conditions.

l_index = sy-tabix.

assign component ranges of structure gs_conditions to <fs>.

CONCATENATE gs_conditions-fieldname 'IN' <fs> INTO lt_condition SEPARATED BY space.

IF sy-tabix > 1.

CONCATENATE 'AND' lt_condition INTO lt_condition SEPARATED BY space.

ENDIF.

APPEND lt_condition.

ENDLOOP.