SQL:How to define the where condition?

[former_member720770]

Select ~

From MSEG

Where the month of MSEG-BUDAT_MKPF = [Analyze period] on selection screen

I just need YYYYMM (6 characters) to be the condition.

How to deal with it?

[FredericGirod]

you must describe the input period variable :

- is it a parameter ?

- is it a select option ? is it limited in intervals / extension ?

[former_member720770]

It is a select-option, and it's not limited, just must be showed as 'MM.YYYY'

[FredericGirod]

you need to convert it to be able to use it in a SELECT statement.

To create a variable like a select-option, you have to use the RANGES type. (press F1 on RANGES).

After that you have to convert SELECT OPTION (it is not so easy). Maybe there is function module that do this for you. Other wise :

- Sign (I or E) you don't need to touch it

- OPTION (EQ, BT, ...) you need to replace it

- LOW (low value)

- HIGH (high value)

for example, if you have something like 08.2020 - 10.2020

inside SAP it will be I BT 202008 202010

you need to convert it in I BT 20200801 20201031

[Sandra_Rossi]

or ABAP >= 7.40 SP08:

SELECT * FROM sflight
    WHERE substring( fldate , 1 , 6 ) = '201712'
    INTO TABLE @DATA(itab).

[former_member720770]

Thanks for your answering!

As your suggestion , if I convert the S_ZYEAR to a RANGE type, what should I fill into the LOW value and the HIGH value ?

I don't know the details of year and month, because it competely set by users

[FredericGirod]

Date in SAP is YYYYMMDD

So if you have YYYY you have to do a concatenation of YYYY MM DD

if you have only the YYYY the select option contains :

I EQ YYYY you have to convert it to I BT YYYY0101 YYYY1231

it is not an easy task, I wonder if it is not better to ask user for date and convert it to year

[former_member720770]

But YYYY MM completely depends on what user selected on the selection screen, in this case, how to convert it to year?

[former_member720770]

or like low '00000101'

high '00001231'?

[jakob_steen-petersen]

What about this:

PARAMETERS: p_period TYPE n LENGTH 6.

DATA(lv_period) = |{ p_period }%|.
SELECT * FROM mseg
  INTO TABLE @DATA(lt_mseg)
  WHERE budat_mkpf LIKE @lv_period.

[manish_shankar]

Hello,

Please check the below code:

DATA : _date_range TYPE RANGE OF mseg-budat_mkpf,
       _start_day  TYPE mseg-budat_mkpf,
       _last_day   TYPE mseg-budat_mkpf.

PARAMETERS : p_monyr TYPE spmon OBLIGATORY.

START-OF-SELECTION.

* First day of period
  _start_day = |{ p_monyr }{ '01' }|.
* Last day of period
  CALL FUNCTION 'RP_LAST_DAY_OF_MONTHS'
    EXPORTING
      day_in            = _start_day
    IMPORTING
      last_day_of_month = _last_day
    EXCEPTIONS
      day_in_no_date    = 1
      OTHERS            = 2.
* Populate date range
  _date_range = VALUE #( BASE _date_range ( sign = 'I' option = 'BT' low = _start_day high = _last_day ) ).
* Pass into select
  SELECT * FROM mseg INTO TABLE @DATA(_t_mseg) WHERE budat_mkpf IN @_date_range.

Best Regards, Manish Shankar.

[former_member720770]

Thank you so much!