topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Tue, 23 Jun 2009 06:21:31 GMT

hi all,

in the select option i have month and year range option as below

SELECT-OPTIONS: s_mon FOR s031-spmon

selection screen:

month = 2008.12 to 2009.02

where as in the select query i need to get inbetween data 12.2008 to 2.2009.

if i put as bleow it doesnot select inbetween data , how to get fetch inbetween.

do i need to write code in intilization.

SELECT mblnr

mjahr

bldat

budat

INTO TABLE gt_mkpf

FROM mkpf

WHERE mblnr in s_mblnr

AND mjahr = s_mon-low+0(4)

and mjahr = s_mon-high+0(4).

please help with code

thanks in advance

Re: select query

Former Member — Tue, 23 Jun 2009 06:25:02 GMT

Hi,

the select option will take care of it by itself...




SELECT mblnr
mjahr
bldat
budat
INTO TABLE gt_mkpf
FROM mkpf
WHERE mblnr in s_mblnr
AND mjahr in s_mon.

Re: select query

Former Member — Tue, 23 Jun 2009 06:26:44 GMT

Hi,

The date format used in the select options is based on the user settings available in SU3 or SU01 defaults -> Date format settings.

So either you can modify your user settings, or based on the format available in the table USR01, take the offset value accordingly from the select options.

Regards,

Santhosh.

Re: select query

Former Member — Tue, 23 Jun 2009 06:29:19 GMT

HI,

Check this way..

SELECT mblnr
mjahr
bldat
budat
INTO TABLE gt_mkpf
FROM mkpf
WHERE mblnr in s_mblnr
AND mjahr GE s_mon-low+0(4)
and mjahr LE s_mon-high+0(4).

RANGES R_MJAHR FOR MKPF-MJAHR.
R_MJAHR-SIGN = 'I'.
R_MJAHR-LOW = S_MON-low+0(4).
IF S_MON-HIGH IS INITIAL.
  R_MJAHR-HIGH = S_MON-HIGH+0(4).
  R_MJAHR-OPTION = 'BT'.
ELSE.
  R_MJAHR-OPTION = 'EQ'.
ENDIF.
APPEND G_MJAHR. CLEAR R_MJAHR.

SELECT mblnr
mjahr
bldat
budat
INTO TABLE gt_mkpf
FROM mkpf
WHERE mblnr in s_mblnr
AND mjahr IN R_MJAHR.

Re: select query

Former Member — Tue, 23 Jun 2009 06:36:09 GMT

Try like this,but you will get between fiscal year 2008 and 2009,not between the dates....

SELECT mblnr

mjahr

bldat

budat INTO TABLE gt_mkpf FROM mkpf

WHERE mblnr in s_mblnr

AND mjahr GE s_mon-low+0(4)

AND mjahr LE s_mon-high+0(4).

Re: select query

Former Member — Tue, 23 Jun 2009 06:37:13 GMT

Hi,

Take two variables.

data: v_date1 type sy-datum,

v_date2 type sy-datum.

concatenate s_mon-low '01' into v_date1.

get the last day of month for the month that you have entered in s_mon-high.

suppose the last day of month is 30 or 31 or 28 or 29.

concatenate s_mon-high <last day of month> into v_date2.

now in select query write as shown below.

SELECT mblnr

mjahr

bldat

budat

INTO TABLE gt_mkpf

FROM mkpf

WHERE mblnr in s_mblnr

AND ( bldat GE v_date1 and

bldat LE v_date2 )

AND (budat GE v_date1 and

budat LE v_date2 )

AND (mjahr between s_mon-low+0(4)

and s_mon-high+0(4)).

Regards,

Kumar Bandanadham

Re: select query

Former Member — Tue, 23 Jun 2009 06:46:43 GMT

Hello Sree

Please follow the steps below.

Range: dat for mkpf-budat.

selection screen:

month = 2008.12 to 2009.02.

CONCATENATE: s_mon-low '.01' TO dat-low.

CONCATENATE: s_mon-high '.28' TO dat-high.

dat-sign = 'I'.

dat-option = 'BT'.

SELECT mblnr

mjahr

bldat

budat

INTO TABLE gt_mkpf

FROM mkpf

WHERE mblnr in s_mblnr

AND BUDAT in DAT.

Hope this solves your problem.

Cheers,

Suvendu

Re: select query

Former Member — Tue, 23 Jun 2009 06:49:42 GMT

Try something like following

TABLES : MKPF,
         S031.
DATA GT_MKPF TYPE TABLE OF MKPF.
SELECT-OPTIONS: S_MBLNR FOR MKPF-MBLNR,
                S_MON   FOR S031-SPMON.


RANGES : R_BUDAT FOR MKPF-BUDAT.

START-OF-SELECTION.
  CLEAR R_BUDAT[].
****LOOP AT SELECTED MONTHS AND CREATE DATES RANGE
  LOOP AT S_MON.
    R_BUDAT-SIGN   = S_MON-SIGN.                 "SET SIGN EQUAL TO SELECTED
    R_BUDAT-OPTION = S_MON-OPTION.               "SET OPTION EQUAL TO SELECTED
    CONCATENATE S_MON-LOW '01' INTO R_BUDAT-LOW. "SET FIRST DATE OF THE MONTH
***********GET LAST DATE OF THE MONTH
    CALL FUNCTION 'DATE_GET_MONTH_LASTDAY'
      EXPORTING
        I_DATE = R_BUDAT-LOW
      IMPORTING
        E_DATE = R_BUDAT-HIGH.
    APPEND R_BUDAT. "APPEND DATE RANGE
  ENDLOOP.
***RUN QUERY
  SELECT MBLNR
         MJAHR
         BLDAT
         BUDAT
    INTO CORRESPONDING FIELDS OF TABLE GT_MKPF
    FROM MKPF
   WHERE BUDAT IN R_BUDAT
     AND MBLNR IN S_MBLNR.