Skip to Content
avatar image
Former Member

ABout SELECT Query

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...???

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 09, 2009 at 01:03 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 09, 2009 at 12:58 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 09, 2009 at 12:59 PM

    HI try this.

    select * from TABLE

    into table itab

    where pspid = v1

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

    and year = v_year.

    Regards,

    Satish

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 09, 2009 at 01:00 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 09, 2009 at 01:06 PM

    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

    Add comment
    10|10000 characters needed characters exceeded