Select from table via a set without duplicate values.

[Former Member]

Hello,

I have the following code that reads selected values from an checkbox group:

DATA lo_nd_chk_ing TYPE REF TO if_wd_context_node.
   DATA lt_chk_ing TYPE wd_this->elements_chk_ing.
   DATA lt_set TYPE wdr_context_element_set.
   DATA ls_set LIKE LINE OF lt_set.
   DATA lv_value TYPE string.
   DATA lv_text TYPE string.
   DATA lo_el_context TYPE REF TO if_wd_context_element.*
   DATA ls_context TYPE wd_this->element_chk_ing.

   lo_nd_chk_ing = wd_context->get_child_node( name = wd_this->wdctx_chk_ing ).
   lo_nd_chk_ing->get_static_attributes_table( IMPORTING table = lt_chk_ing ).

   CALL METHOD lo_nd_chk_ing->get_selected_elements
     RECEIVING
       set = lt_set.

   LOOP AT lt_set INTO ls_set.
     CALL METHOD ls_set->get_attribute
       EXPORTING
         name  = 'ING_NAAM'
       IMPORTING
         value = lv_value.

*    CONCATENATE lv_value lv_text INTO lv_text SEPARATED BY ' '.

   ENDLOOP.

With the values from the set I have to do a select on a table and get the values.

So normally without the set the code would look like:

Select * from tbl_broodjeing

into lt_broodjeing

where ing_id = '1' OR ing_id = '2'.

But the amount of wheres are depended on the number of values in the set.

So what I get back is data like this:

mandt - broodje_id - ing_id

100 - 1 - 1

100 - 1 - 2

100 - 2 - 1

100 - 2 - 2

But I want to get back only 2 rows in this case so I want this:

mandt - broodje_id

100 - 1

100 - 2

If you have any more questions please feel free to ask.

Kind regards,
Vincent

[amy_king · Accepted solution]

Hi Vincent,

Try using SELECT DISTINCT combined with only the fields you're interested in querying...

SELECT DISTINCT mandt broodje_id
       FROM tbl_broodjeing
       INTO CORRESPONDING FIELDS OF TABLE lt_broodjeing
       WHERE ing_id IN ( '1', '2' ).

Cheers,

Amy

[Former Member · Accepted solution]

Thanks for your input.

I have added some code:

DATA lo_nd_chk_ing TYPE REF TO if_wd_context_node.
   DATA lt_chk_ing TYPE wd_this->elements_chk_ing.
   DATA lt_set TYPE wdr_context_element_set.
   DATA ls_set LIKE LINE OF lt_set.
   DATA lv_value TYPE string.
   DATA lv_text TYPE string.
   DATA lo_el_context TYPE REF TO if_wd_context_element.
   DATA ls_context TYPE wd_this->element_chk_ing.
   DATA: lt_temp_ing TYPE TABLE OF ztbl_ing.
   DATA: lt_selected_ing TYPE TABLE OF ztbl_ing.
   DATA: lw_selected_ing TYPE ztbl_ing.


   lo_nd_chk_ing = wd_context->get_child_node( name = wd_this->wdctx_chk_ing ).
   lo_nd_chk_ing->get_static_attributes_table( IMPORTING table = lt_chk_ing ).

   CALL METHOD lo_nd_chk_ing->get_selected_elements
     RECEIVING
       set = lt_set.

   LOOP AT lt_set INTO ls_set.
     CALL METHOD ls_set->get_attribute
       EXPORTING
         name  = 'ING_NAAM'
       IMPORTING
         value = lv_value.

     SELECT * FROM ztbl_ing
     INTO TABLE lt_temp_ing
     WHERE ing_naam EQ lv_value.

     APPEND LINES OF lt_temp_ing TO lt_selected_ing.

   ENDLOOP.

   SELECT DISTINCT broodje_id
     FROM ztbl_broodjeing
     INTO lt_temp
     WHERE ing_id EQ "all lines of lt_selected_ing-ing_id

I have to select all the values from table ztbl_broodjeing  where the field ing_id corresponds with the values from lt_selected_ing-ing_id.

Is it best practice to create a join or how is this done?

Kind regards,

Vincent

[amy_king · Accepted solution]

Hi Vincent,

You can achieve this with a JOIN between tables ZTBL_ING and ZTBL_BROODJEING or you can do the queries individually as you have written above and use the addition FOR ALL ENTRIES in your query of ZTBL_BROODJEING.

In general, I recommend not placing a query within a LOOP... ENDLOOP for performance reasons. If the number of iterations of the loop is small, it's not a problem and you won't have performance issues, but if the number is large it can affect performance.

From your code, it looks like you want your end result to be a table of unique BROODJE_ID values-- is this correct? If so, another approach could be to first build a range table of ING_NAAM values and then use the range table in a join between tables ZTBL_ING and ZTBL_BROODJEING to read the data you want.

SELECT DISTINCT broodje_id
       FROM ztbl_broodjeing
       JOIN ztbl_ing ON ztbl_ing~xxx = ztbl_broodjeing~yyy
       INTO TABLE lt_broodjeing
       WHERE ing_id IN range_table_ing_id.

Depending on the structures of your z-tables, you may or may not need the DISTINCT keyword to omit duplicates.

Cheers,

Amy