ABout SELECT Query

[Former Member]

Hi All,

I have a custom table where user maintains the data in the following format.

project month year amt1 amt2 amt3

proj1 april 2009 100.00 25.50 356.00

proj1 may 2009 100.00 25.50 356.00

proj1 june 2009 100.00 25.50 356.00

Now i would like to query this table based on Project, Month and Year.

select * from TABLE

into table itab

where pspid = v1

and month between v_month1 and v_month2

and year = v_year.

Here v_month1 = april and v_month2 = june.

Hence i would like to get the data from month april to june (april, may and june rows).

How to write code for this selection...???

[Former Member]

Hi,

see the sample code.

 select * from TABLE
 into table itab
 where pspid = v1
 and month in ( 'april ' , 'may', 'June').
 and year = v_year.
"Itab contains april may june data

or try this..

 select * from TABLE
 into table itab
 where pspid = v1
 and month between v_month1 and v_month2
 and year = v_year.

loop at itab where v_month1in ( 'april ' , 'may', 'June').

append to t_output.

endloop. 

or try tjis way..

loop at itab .
if v_month1= 'april '  or v_month1=  'may' or v_month1= 'June'.
continue.
else.
delete itab index sy-tabix.
endif.
endloop. 
"Now the itab contains only april may june months data..

Prabhudas

Edited by: Prabhu Das on Jul 9, 2009 6:29 PM

[Former Member]

HI try this.

select * from TABLE

into table itab

where pspid = v1

and month In ( 'april' , 'may' , 'June' )

and year = v_year.

Regards,

Satish

[Former Member]

oh well, you better had made up your mind when creating this custom table. now its too late and you gotta live with it.

do you really have "april 2009" in your table? cause then you can simple forget about selecting anything of basis on Date.

all you can do is to select all, and when looping over your itab you can try to meet the conditions by string operations.

[Former Member]

Hi,

The easy answer is to use the month number in the table rather than the name, but I guess you are where you are.

What you'll need to do is convert the from and to month to numbers (e.g. april = 04, june = 06).

Then turn this range into a list (e.g. 04, 05, 06).

Then turn the list of numbers into a list of months (e.g. april, may, june)

In your select statement you should then check that month is in this list.

But I'd just redesign the table.

Regards,

Nick

[Former Member]

Hi Pavan,

In this case, you can't compare the month text and use between operator.

You can do it by changing the month (char type size 2) field in the custom table and write query based on that.

ex: if the selection is made for april & june then maintain a internal table(say i_month) with fields index, month text and read this internal table, use the index value in the select query

If you don't want to change the custom table, then you can use the internal table(i_month) for month with index, read the index for the selection (say you got 04 for april and 06 for june). Now

loop at i_month where index GE 04 and index LE 06.

maintain a range table for month and populate it here

endloop.

Use this month range field in the select query

Thanks,

Muthu

Edited by: Muthu Prabakaran Selvam on Jul 9, 2009 6:40 PM