topic Re: Select-options issue in Application Development and Automation Discussions

Select-options issue

Former Member — Wed, 27 Aug 2008 06:46:10 GMT

Hi,

I have a select-option for dates, I want to select data from a table which has a valid from and valid to date fields for records.

The entered dates or date range are to be taken as multiple single dates and records that are valid for any of the entered dates are to be selected. How can I do that??

Thanks in Advance,

Hitesh

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 06:48:44 GMT

suppose your select options is named as S_DATE

select * from <dbtab> into table itab where date in S_DATE.

the records with date in the range of s_date will be selected. it will be handled internally.

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 06:50:17 GMT

hi Hitesh K

what is the issue by using IN operator for SELECT-OPTION in WHERE Condition

Regards

Deva

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 06:52:22 GMT

Hi,

Theres a valid from and a valid to date in the database table and if any of the entered dates lie in between these dates, the record has to be selected. The problem is that entered date is not a parameter but select-option.

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 06:54:43 GMT

In the WHERE condition of the select statement try to give as follows,

WHERE validfrom IN s_selopt

AND validto IN s_selopt.

The select options will have the following data in them (if you are giving a validity date range)

SIGN = I

OPTION = BT

LOW = Valid from date

HIGH = Valid to date

So the selected data will be restricted to the date range you are giving.

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 06:59:59 GMT

Hi Avinash,

Let us say that there a record with valid from date as 01.01.2008 and valid to date as 31.01.2008. And user enters 02.01.2008 to 30.01.2008 in the select-option. I want this record to be selected but your select will not pick the record. Hope you all understand the difference.

Thanks,

Hitesh

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 07:01:40 GMT

Hi hitesh ,

Try this

SELECT * FROM db_name INTO itab_name
FOR ALL ENTRIES IN S_DATE 
WHERE S_DATE-LOW GE VALID_FROM AND
S_DATE-HIGH LE VALID_TO.

This might work as all select-options values are stored in an itab with the select-options name.

Check this and let me know...

Regards

Karthik D

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 07:05:40 GMT

Hi Karthik,

When you can have multiple ranges and/or multiple single values in select-options this will not work.

In case where high is blank.

Hope you get it.

Thanks for trying,

Hitesh

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 07:06:45 GMT

Hi,

As the date is select-options i.e. s_date we can have query in this way :

SELECT f1 f2..

FROM databasetable

WHERE validfrom IN s_date

AND validto IN s_date.

s_date will have values of datefrom and dateto in this way :

SIGN = I

OPTION = BT

LOW = datefrom

HIGH = dateto

the above query will give u data for the given date range.

thanx.

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 07:06:59 GMT

Of course it wouldnt pick up the record as you said because the valid from and valid to date range is out of the date range that the user entered.

You can go for this instead

WHERE validfrom GE s_selopt-low

AND validto LE s_selopt-high.

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 07:09:46 GMT

Hi hitesh,

Then noway, u need to loop at select-options tables, check the sign to determine whether the user have entered multiple or single values. Then write select query to fetch and store it in a separate itab.

regards

Karthik D

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 07:12:48 GMT

Hi,

for Dhanashri - Thank you for trying but when you say valid-from in s_date it means valid from date should be in entered date in select option (one of the entered date should actually be equal to valid from date) and not entered date lies in between the valid from and valid to dates. Hope u understand the difference.

Thanks,

Hitesh

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 07:29:38 GMT

Hi Hitesh,

In my understanding, the option given previously by Avinash should solve your problem.

valid date must be greater than s_option-low and less than s_option-high.

Hope this helps,

Erwan

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 07:34:54 GMT

Hi,

U r right we can then use this way :

SELECT f1 f2..

FROM dbtable

INTO internaltable

WHERE validfrom GE s_date-low

AND validto LE s_date-high.

hope this helps.

thanx.

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 08:04:39 GMT

Hi,

Dhanashri and Erwan - the select will fail in case of multiple ranges and single values where high is blank.

I guess Karthik' solution can be used by removing excluded ranges and excluded single values options from the select option.

Thank you,

Hitesh

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 08:05:31 GMT

well this is quite easy. make yourself a small painting on a paper.


         |------------------------|
        so1                   so2

    |---------------------------------------------|
  validfrom                                 validto

so if you want to know if any of the dates between datefrom and dateto are within the intervall of you given selectoptions you gotta revert logic.

if validto greater equal so1 (select-option-low) and validfrom lesser equal so2 (select-option-high). then you have a hit.

so:


select * 
from   dbtab
into   table itab
where  validfrom le select-option-low
and    validto ge select-option-high.

Edited by: Florian Kemmer on Aug 27, 2008 10:05 AM

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 08:08:08 GMT

Thank you Florian,

But please see my previous comment. in case of single values and multiple ranges, this select will not work as required.

Thanks,

Hitesh

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 08:15:40 GMT

Humm....

Hitesh, I think you should restrict the possibilities of your select option :

1- Use 2 parameters ( they will correspond to p_low and p_High ).

2- Use the FM 'SELECT_OPTIONS_RESTRICT'

eg :


*&---------------------------------------------------------------------*
*&      Form  init
*&---------------------------------------------------------------------*
FORM init.

  MOVE 'ALL'        TO opt_list-name.
  MOVE 'X' TO: opt_list-options-bt,
               opt_list-options-eq.
  APPEND opt_list TO restrict-opt_list_tab.

  CLEAR ass.
  MOVE: 'B'          TO ass-kind,
        'KOSTL_RANGE_SET' TO ass-name,
        'I'          TO ass-sg_main,
        ' '          TO ass-sg_addy,
        'ALL'        TO ass-op_main,
        'ALL'        TO ass-op_main.
  APPEND ass TO restrict-ass_tab.

  CALL FUNCTION 'SELECT_OPTIONS_RESTRICT'
       EXPORTING
            restriction            = restrict
       EXCEPTIONS
            too_late               = 1
            repeated               = 2
            selopt_without_options = 3
            selopt_without_signs   = 4
            invalid_sign           = 5
            empty_option_list      = 6
            invalid_kind           = 7
            repeated_kind_a        = 8
            OTHERS                 = 9.
  IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

ENDFORM.                    " init

Hope this helps,

Erwan

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 08:16:57 GMT

well you gotta do this for every record in your select options.

declare yourself a ranges structure (ranges are similar to select options)

then loop your select options table in your structure.

then check if low or high value are missing.

if low = space then low 0 high.

and if high = space then high = low.

this will make the select work as designed.

tho that you do it now more than one time (estimated you got more records in your select options, multiple values) you will have more than one select.

since your second select will destroy data from first select, i´d advise you to select into an internal table, then after the select append all those entries in there into a second internal table.

then again select in your first itab and so on...

got me?

Re: Select-options issue

Former Member — Wed, 27 Aug 2008 09:03:44 GMT

Hi,

Thank you Erwan for that FM.

Thank you all for your help.

I've decided to do this by removing the exclude single values and exclude ranges from select-options using Erwans suggested FM and using the method suggested by Karthik. I think that should work.

Once again thank you all.

Thanks,

Hitesh