topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Tue, 26 Dec 2006 04:25:00 GMT

hi abapers,

i want to select rows from the db table with month as the selection criteria .

the object is as follows :

parameter : date like sy-datum obligatory .

select * from tcurr into it where gdatu = date

here the date must fall within the month of the selection screen parameters.

ie if date = 26.12.2006, then the month is december and hence i have to retrieve all the rows from the table TCURR based upon the condition that the GDATU field must fall in the month of only december .

i have tried this but it didn't work out .

eg : select * from tcurr where gdatu = '12' .

can anybody can give me a hint .

thanx in advance .

anil

Re: select statement

Former Member — Tue, 26 Dec 2006 04:27:16 GMT

hi,

use like statement instead of = and use '%' instead of '*'.

Re: select statement

Former Member — Tue, 26 Dec 2006 04:28:08 GMT

Option 1 :

Get the first and last dates of that month, using the date entered, and the fire the select

select * from tcurr into it where gdatu between first_date and last_date.

Option 2:

Get all the rows and then filter. This one will have performance issue.

Regards,

Ravi

Note - Please mark all the helpful answers

Re: select statement

Former Member — Tue, 26 Dec 2006 04:28:37 GMT

Let gdatu be 20061226 and date be 20061223.

Modify your select statement's where clause as below

gdatu4(2) = date4(2).

Re: select statement

Former Member — Tue, 26 Dec 2006 04:28:49 GMT

Hi,

Get the first day and last day of that month..

And then use it in the SQL..

Example.

DATA: V_FIRST TYPE SYDATUM.

DATA: V_LAST TYPE SYDATUM.

SELECT * FROM TCURR INTO TABLE IT

WHERE GDATU >= V_FIRST AND

AND GDATU <= V_LAST.

Thanks,

Naren

Re: select statement

Former Member — Tue, 26 Dec 2006 04:33:28 GMT

hi,

try this

select * from tcurr where gdatu like '%12%' .

i hope this will help u.

Re: select statement

Former Member — Tue, 26 Dec 2006 04:36:03 GMT

Hi Anil ,

The date table TCURR is in inverted format , so you need to first get the date in inverted format .

First what you need to do is get the first and last date of the month , , convert this into inverted way and created a range using this set of inverted dates and use this in your select statement.

Use the FM RP_LAST_DAY_OF_MONTHS , to get the last day of the month , the first day can be obtained by changing the date to 01 , ie last 2 digits of the date to 01

e.g. if date is v_date , the the command

v_first = v_date.

v_first+6(2) = '01'.

Once you got the first and last day of the month use the FM CONVERSION_EXIT_INVDT_INPUT , to convert the date into invertef format.

Create a range using these values and use this range in select statement.

Please revert back in case of queries .

Regards

Arun

Reward points if reply is found useful

Re: select statement

Former Member — Tue, 26 Dec 2006 04:37:40 GMT

Hi,

Check this example..

parameters: p_date type sydatum.

data: v_last type sydatum.

data: v_first type sydatum.

start-of-selection.

Get the last day of the month.

CALL FUNCTION 'RP_LAST_DAY_OF_MONTHS'

EXPORTING

day_in = p_date

IMPORTING

LAST_DAY_OF_MONTH = v_last.

Get the first day of the month.

v_first = p_date.

v_first+6(2) = '01'.

Get the data.

SELECT * FROM TCURR INTO TABLE IT

WHERE GDATU >= V_FIRST

AND GDATU <= V_LAST.

Thanks,

Naren

Re: select statement

Former Member — Tue, 26 Dec 2006 04:41:00 GMT

thanx guys for a quick reply