topic Re: Retrieve data from database in Application Development and Automation Discussions

Retrieve data from database

Former Member — Thu, 26 Feb 2009 04:17:57 GMT

Hi friends,

I need a logical solution.

I have 10 yrs employees data. I have upload only basic salary through bdc in database table.

I have a MODULE POOL screen, On screen month ,year and emp. no. (3 text fields) is available.

I have several records of April(2001,2002........2009) ,May(2001,2002, 2003.......2009)..........Jan(2001,2002, 2003..........2009) . To neglect the overwrite of emp no. i have taken (empno, month, year) as aprimary key in database table.

My requirement is When i input month, year and employee no. on screen of particular employee the whole data of current financial year (april 2008 to jan 2009) should be selected from the database table. Not a previous year (2006, 2007, 2008).

One more thing we need to consider if we r extracting data of same year means we want data till Dec.2008 the data should be extract from april 2008 to dec 2008. If we wanna data Till Jan. 2009 the should be retrieve April 2008 to Jan 2009. Not be previous finacial year.

Plz reply as soon as possible.

Regards,

Swapnika

Re: Retrieve data from database

Former Member — Thu, 26 Feb 2009 04:34:30 GMT

Hi Swapnika

To achieve this first you create an internal table and pass month data to it based on current month like this :

TYPES : BEGIN OF tp_month,

cmonth TYPE znel_perform-cmonth,

END OF tp_month.

t_month TYPE STANDARD TABLE OF tp_month WITH HEADER LINE,

Get the current month in a variable .

l_month = sy-datum+4(2).

IF l_month EQ '01'.

CLEAR : l_rows.

l_rows = 09.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

t_month-cmonth = 'JULY'.

APPEND t_month.

t_month-cmonth = 'AUGUST'.

APPEND t_month.

t_month-cmonth = 'SEPTEMBER'.

APPEND t_month.

t_month-cmonth = 'OCTOBER'.

APPEND t_month.

t_month-cmonth = 'NOVEMBER'.

APPEND t_month.

t_month-cmonth = 'DECEMBER'.

APPEND t_month.

ELSEIF l_month EQ '02'.

CLEAR : l_rows.

l_rows = 10.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

t_month-cmonth = 'JULY'.

APPEND t_month.

t_month-cmonth = 'AUGUST'.

APPEND t_month.

t_month-cmonth = 'SEPTEMBER'.

APPEND t_month.

t_month-cmonth = 'OCTOBER'.

APPEND t_month.

t_month-cmonth = 'NOVEMBER'.

APPEND t_month.

t_month-cmonth = 'DECEMBER'.

APPEND t_month.

t_month-cmonth = 'JANUARY'.

APPEND t_month.

ELSEIF l_month EQ '03'.

CLEAR : l_rows.

l_rows = 11.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

t_month-cmonth = 'JULY'.

APPEND t_month.

t_month-cmonth = 'AUGUST'.

APPEND t_month.

t_month-cmonth = 'SEPTEMBER'.

APPEND t_month.

t_month-cmonth = 'OCTOBER'.

APPEND t_month.

t_month-cmonth = 'NOVEMBER'.

APPEND t_month.

t_month-cmonth = 'DECEMBER'.

APPEND t_month.

t_month-cmonth = 'JANUARY'.

APPEND t_month.

t_month-cmonth = 'FEBRUARY'.

APPEND t_month.

ELSEIF l_month EQ '04'.

CLEAR : l_rows.

l_rows = 1.

t_month-cmonth = 'APRIL'.

APPEND t_month.

ELSEIF l_month EQ '05'.

CLEAR : l_rows.

l_rows = 1.

t_month-cmonth = 'APRIL'.

APPEND t_month.

ELSEIF l_month EQ '06'.

CLEAR : l_rows.

l_rows = 2.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

ELSEIF l_month EQ '07'.

CLEAR : l_rows.

l_rows = 3.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

ELSEIF l_month EQ '08'.

CLEAR : l_rows.

l_rows = 4.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

t_month-cmonth = 'JULY'.

APPEND t_month.

ELSEIF l_month EQ '09'.

CLEAR : l_rows.

l_rows = 5.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

t_month-cmonth = 'JULY'.

APPEND t_month.

t_month-cmonth = 'AUGUST'.

APPEND t_month.

ELSEIF l_month EQ '10'.

CLEAR : l_rows.

l_rows = 6.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

t_month-cmonth = 'JULY'.

APPEND t_month.

t_month-cmonth = 'AUGUST'.

APPEND t_month.

t_month-cmonth = 'SEPTEMBER'.

APPEND t_month.

ELSEIF l_month EQ '11'.

CLEAR : l_rows.

l_rows = 7.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

t_month-cmonth = 'JULY'.

APPEND t_month.

t_month-cmonth = 'AUGUST'.

APPEND t_month.

t_month-cmonth = 'SEPTEMBER'.

APPEND t_month.

t_month-cmonth = 'OCTOBER'.

APPEND t_month.

ELSEIF l_month EQ '12'.

CLEAR : l_rows.

l_rows = 8.

t_month-cmonth = 'APRIL'.

APPEND t_month.

t_month-cmonth = 'MAY'.

APPEND t_month.

t_month-cmonth = 'JUNE'.

APPEND t_month.

t_month-cmonth = 'JULY'.

APPEND t_month.

t_month-cmonth = 'AUGUST'.

APPEND t_month.

t_month-cmonth = 'SEPTEMBER'.

APPEND t_month.

t_month-cmonth = 'OCTOBER'.

APPEND t_month.

t_month-cmonth = 'NOVEMBER'.

APPEND t_month.

ENDIF.

Now when you loop data read this table and check if month belongs to any of the months in this table a

and then proceed .

You can even use ranges instead of a table to achieve this and add it as a condition in select .

If any issues update

Re: Retrieve data from database

Former Member — Thu, 26 Feb 2009 04:34:33 GMT

I have a question.

1. When you are selecting the data from April to Dec or April to Jan why you are giving option of Month in Input screen. You can give only Employee id and Year. Let me confirm ur requirement if user enters

Dec in input screen then should u bring from April to Dec ?

you can fetch data based on Employee id and Year, once if you have to fecth data from April to Dec,

then read your internal table with the months you required and populate.

Re: Retrieve data from database

Former Member — Thu, 26 Feb 2009 05:06:42 GMT

hi,

According to me the logic would be like this:

select-options: s_month-low & s_month-high, s_year-low & s_year-high, s_emp_no.

1. write in PBO.

data: ldate like sy-datum.

lyear type char4.

vdate = sy-datum+4(4). ******this will give u current year.

START-OF-SELECTION.

if s_year-low or s_year-high = lyear.

select -

ur fields -

from -

into -

where s_month BETWEEN '04' AND '01'

and s_year BETWEEN ( lyear - 1) AND lyear

and emp in s_emp_no.

if we calculate for same year like 2008, than it will fetch data from april to dec. 2008 or in one of the select option we give the value ******

elseif ( s_year-low = s_year-high ) or s_year NE 0 .

select -

ur fields -

from -

into -

where month BETWEEN '04' AND '12'

and year in s_year

and emp in s_emp_no.

elseif s_month-high = '01'

select -

ur fields -

from -

into -

where s_month BETWEEN '04' AND '01'

and s_year BETWEEN ( lyear - 1) AND lyear

and emp in s_emp_no.

endif.

Probably this could be the nearest solution. i have written logic only, try to implement like this.

Cheers,

Rudhir

Re: Retrieve data from database

Former Member — Thu, 26 Feb 2009 05:18:32 GMT

Dear Swapnika,

take year as a select option.

month as a select option

and emp as a select option.

and code is.*****************************

tables: vbrk.

select-options: s_year for vbrk-gjahr obligatory,

s_month for vbrk-inco1 obligatory,

s_empno for table-pernr.

data: py type gjahr,

cy type gjahr,

lin type i,

count type i.

data: begin of jtab occurs 0,

month like vbrk-inco1,

year like vbrk-gjahr,

end of jtab.

start-of-selection.

py = s_year-low.

cy = s_year-high.

jtab-month = 'APR'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'MAY'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'JUN'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'JULY'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'AUG'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'SEP'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'OCT'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'NOV'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'DEC'.

jtab-year = py.

append jtab.

clear jtab.

jtab-month = 'JAN'.

jtab-year = cy.

append jtab.

clear jtab.

jtab-month = 'FEB'.

jtab-year = cy.

append jtab.

clear jtab.

jtab-month = 'MAR'.

jtab-year = cy.

append jtab.

clear jtab.

read table jtab with key month = s_month-high.

count = sy-tabix.

data: count1 type i.

count1 = count + 1.

delete jtab from count1.

In select query jus do

select * from ztable into table itab for all entris in jtab where

month = jtab-month

and year = jtab-year and

emp in s_empno.

you will get the desired result

regards

vijay