topic Re: SQL:How to define the where condition? in Application Development and Automation Discussions

SQL:How to define the where condition?

Former Member — Mon, 11 Jan 2021 08:34:09 GMT

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?

Re: SQL:How to define the where condition?

FredericGirod — Mon, 11 Jan 2021 08:43:56 GMT

you must describe the input period variable :

- is it a parameter ?

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

Re: SQL:How to define the where condition?

Former Member — Mon, 11 Jan 2021 08:52:45 GMT

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

Re: SQL:How to define the where condition?

FredericGirod — Mon, 11 Jan 2021 09:21:47 GMT

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

Re: SQL:How to define the where condition?

Sandra_Rossi — Mon, 11 Jan 2021 10:59:15 GMT

or ABAP >= 7.40 SP08:

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

Re: SQL:How to define the where condition?

Former Member — Tue, 12 Jan 2021 05:37:52 GMT

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

Re: SQL:How to define the where condition?

FredericGirod — Tue, 12 Jan 2021 06:45:42 GMT

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

Re: SQL:How to define the where condition?

Former Member — Tue, 12 Jan 2021 06:57:25 GMT

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

Re: SQL:How to define the where condition?

Former Member — Tue, 12 Jan 2021 07:02:35 GMT

or like low '00000101'

high '00001231'?

Re: SQL:How to define the where condition?

jakob_steen-petersen — Tue, 12 Jan 2021 07:18:48 GMT

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.

Re: SQL:How to define the where condition?

manish_shankar — Tue, 12 Jan 2021 13:38:25 GMT

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.

Re: SQL:How to define the where condition?

Former Member — Wed, 13 Jan 2021 02:06:34 GMT

Thank you so much!