topic Re: Select a range in Application Development and Automation Discussions

Select a range

Former Member — Mon, 15 Jan 2007 21:01:20 GMT

How do I select a range of values? For instance, I need to write a select statement that, among others, selects documents based on a date range. What I am doing is trying to pick billing documents that have a billing date within the last 365 days. Also I need to figure out a way (an easy way) to determine if a particular day is a weekend.

Regards,

Aaron

Re: Select a range

RichHeilman — Mon, 15 Jan 2007 21:04:29 GMT

Hi Aaron, for you first requirement, you can simply use a RANGE.

Ranges:  r_datum for sy-datum.

r_datum-sign = 'I'.
r_datum-optin = 'BT'.
r_datum-low = sy-datum - 365.
r_datum-high = sy-datum.
append r_datum.


select * into table itab
                  from ztab
                           where datum in r_datum.

Regards,

Rich Heilman

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:04:40 GMT

Aaron

data: YearAgo like sy-datum.

YearAgo = sy-datum - 365.

select * from SomeTable into i_table

where BillDate >= YearAgo

and BillDate <= sy-datum.

Re: Select a range

RichHeilman — Mon, 15 Jan 2007 21:07:14 GMT

For your second requirement, you can use the function module DATE_TO_DAY to convert each date to the week day. Then you can check the return value for 'Sat.' or 'Sunday'.

REgards,

Rich Heilman

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:09:08 GMT

RANGES: s_date FOR ekko-aedat.

DATA: lv_day TYPE scal-indicator.

s_date-high = sy-datum.

s_date-low = sy-datum - 365.

s_date-option = 'BT'.

s_date-sign = 'I'.

APPEND s_date.

DATA: t_ekko TYPE STANDARD TABLE OF ekko.

SELECT * FROM ekko

INTO TABLE t_ekko

WHERE aedat IN s_date.

CALL FUNCTION 'DATE_COMPUTE_DAY'

EXPORTING

date = sy-datum

IMPORTING

day = lv_day.

IF sy-subrc = 0.

ENDIF.

LV_DAY = '6' for saturday and '7' for sunday.

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:10:32 GMT

Hello Rich,

First off, thanks for the quick reply. After I set up the range, as you outlined, can I add other requirements on the select statement like normal? I assume I can. One more thing (it’s related). I am doing a select off the table VBRP_VRPMA but that is a joined table. Can I treat this table like any other table? I am attempting to determine the 5 largest, consecutive, shipping days per Mat#. Do you know of a better way to do this besides going through every billing document one at a time? Sorry for all of the questions.

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:14:45 GMT

> can I add other

> requirements on the select statement like normal? I

> assume I can.

Yes you can

> doing a select off the table VBRP_VRPMA but that is a

> joined table. Can I treat this table like any other

> table?

Yes u can.

Aaron, by 5 largest and consecutive shipping days what do you exactly mean? Can you illustrate your requirement with sample data?

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:25:10 GMT

Ok, thanks to all for the wonderful help! I have one quick addition to this question before I mark it answered. Right now, as it stands, I have (theoretically) a list of document numbers (with their data) that is in a date range (the last 365). I then want to restrict the material numbers to certain material groups. Each time this report is run the material groups will change and it could be one to all material groups. How would I do a "range" in this instance?

Thanks,

Aaron

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:27:28 GMT

I am not sure i follow your question. You can provide material range as part of the selection screen and then include it in your selection statement..

Re: Select a range

RichHeilman — Mon, 15 Jan 2007 21:30:58 GMT

If the material groups are going to change, I would suggest using a SELECT-OPTION in the selection screen instead of the RANGE. There is no difference between the two other than the SO, will produce fields on the selection-screen. So you can put the values in the selection screen as single entries and save this as a screen variant. Then when the material groups change, you can simply change the variant without having to change the program coding.

Regards,

Rich HEilman

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:33:33 GMT

Thanks Rich. When I use the SO is each field on the Selection Screen a different variable? What I'm getting at is how would I put them in a select statement?

Re: Select a range

RichHeilman — Mon, 15 Jan 2007 21:37:48 GMT

Say that you want to select data based on one or more material groups. You would set up the SELECT-OPTION, then on the screen, add the material groups that you want. Then you just use the IN operator like you did for the RANGE.

tables: mara.

Select-options: s_matkl for mara-matkl.


select * into table it023
                from t023
                         where matkl in s_matkl.

Regards,

Rich Heilman

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:39:05 GMT

you would define a select option (assuming u are selecting from material master)

select-optionS s_matkl for mara-matkl.

SELECT * from MARA

where matkl in S_MATKL.

When u define a select option, you can enter multiple values by clicking on the button at the end of your input box.

Re: Select a range

Former Member — Mon, 15 Jan 2007 21:41:38 GMT

Rich, thank you SO MUCH for your help! I appreciate the help everybody gave me!

Thanks again,

Aaron