topic Re: RETRIEVE DATA FROM DATABASE in Application Development and Automation Discussions

RETRIEVE DATA FROM DATABASE

Former Member — Wed, 25 Feb 2009 11:26:53 GMT

Hi Experts,

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 date(23.02.2009 format) field and emp. no. 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 date on screen like 23.02.2009 and employee no. 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).

Plz reply as soon as possible.

Regards,

Swapnika

Re: RETRIEVE DATA FROM DATABASE

amit_khare — Wed, 25 Feb 2009 11:32:36 GMT

Try this FM - /IBS/RB_FI_GJAHR_DETERMINE

Re: RETRIEVE DATA FROM DATABASE

christine_evans — Wed, 25 Feb 2009 11:35:08 GMT


> Hi Experts,
> 
> 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 date(23.02.2009 format) field and emp. no. 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 date on screen like 23.02.2009  and employee no. 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). 
> 
> Plz reply as soon as possible.
> 
> Regards,
> Swapnika

Work out the start and end dates of the financial year from the date entered (there's probably a function module to do this or it is pretty easy to do yourself) and use BETWEEN with the dates in the select.

Re: RETRIEVE DATA FROM DATABASE

viquar_iqbal — Wed, 25 Feb 2009 11:43:42 GMT

you can write like this in PAI event

data : t_atab like standard table of sflight,
w_c(7) type c value '2008',
w_d(7) type c value '2009'.
CONCATENATE '%' w_c  '%' INTO w_c.
CONCATENATE '%' w_d  '%' INTO w_d.select * from sflight into table t_atab where fldate like w_c or fldate like  w_d .

This will select the records of the year 2008 only

Regard,

Viquar Iqbal

Re: RETRIEVE DATA FROM DATABASE

Former Member — Wed, 25 Feb 2009 11:53:34 GMT

Hi Swapnika,

I could not get what exactly you want.

But you can use following function module to calculate date.

call function '/SAPHT/DRM_CALC_DATE'

exporting

date = &1

days = &2

months = &3

sign = '-'

years = &4

importing

calc_date = &5.

Also to calculate data for whole year, you just replace first 4 characters and get first day and last day of the year.

e.g. v_date = 20090223

then concatenate v_date+0(4) '0101' into v_first_date.

concatenate v_date+0(4) '1231' into v_last_date.

Let me know exactly what do you want.

Regards,

Anil

Re: RETRIEVE DATA FROM DATABASE

keerthy_k — Wed, 25 Feb 2009 11:58:37 GMT

Hi Swapnika,

U need to change the query. Here when u are entering a date like 23.02.2009, then u need the records from April 2008 to Jan 2009.

DATA: frm_date type string,

to_date type string,

l_year TYPE string,

l_year1 type n.

l_year = sy-datum+0(4).

CONCATENATE l_year '01' '01' INTO to_date.

l_year1 = l_year.

l_year1 = l_year1 + 1.

l_year = l_year1.

CONCATENATE l_year '04' '01' INTO frm_date.

select.................

into...

where frm_date = frm_date

and to_date = to_date.

Keerthi.

Re: RETRIEVE DATA FROM DATABASE

Former Member — Wed, 25 Feb 2009 12:12:15 GMT

HI Keerthy,

Every end of month the data is uploaded by user. Means January data will upload 31 jan and Feb data would be upload 28 feb.

If m giving in screen january 2009 the the whole data from April 2008 to january 2009 should be extract.

Regards

Swapnika

Re: RETRIEVE DATA FROM DATABASE

keerthy_k — Wed, 25 Feb 2009 12:18:18 GMT

Hi ,

DATA: frm_date type string,

to_date type string,

l_year TYPE string,

l_year1 type n.

l_year = sy-datum+0(4).----> will give the current year(say 2009)

CONCATENATE l_year '01' '01' INTO to_date.

l_year1 = l_year.

l_year1 = l_year1 - 1.-------> will give the previous year(2008)

l_year = l_year1.

CONCATENATE l_year '04' '01' INTO frm_date.

select.................

into...

where frm_date = frm_date

and to_date = to_date.

hope this will help u..

Keerthi.

Re: RETRIEVE DATA FROM DATABASE

Former Member — Wed, 25 Feb 2009 12:39:45 GMT

Yes Keerthy, exactly u r getting my point, but one more thing when the Feb data would be upload

the 01 will increse 02 thatswhy we couldn't fix month as 01. For this what can we do. and i have taken char type in SCREEN field . Is it ok according to ur code.

Thanks & Regards,

Swapnika

Re: RETRIEVE DATA FROM DATABASE

keerthy_k — Thu, 26 Feb 2009 04:48:45 GMT

Hi Swapnika,

Lets rearrange the logic once again.

DATA: frm_date type string,

to_date type string,

l_year TYPE string,

l_year1(4) type n,

l_month type string,

last_day type sy-datum.

l_year = sy-datum+0(4).----> will give the current year(say 2009)

If u need the month dynamically then,

l_month = sy-datum+4(2).

CONCATENATE l_year l_month '01' INTO to_date.

and if u need the last date of a month,

then u can use the FM

CALL FUNCTION 'SLS_MISC_GET_LAST_DAY_OF_MONTH'

EXPORTING

day_in = sy-datum

IMPORTING

last_day_of_month = last_day

EXCEPTIONS

day_in_not_valid = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

l_year1 = l_year.

l_year1 = l_year1 - 1.-------> will give the previous year(2008)

l_year = l_year1.

CONCATENATE l_year '04' '01' INTO frm_date.

select.................

into...

where frm_date = frm_date

and to_date = to_date.

hope this will help u..

Keerthi.

Edited by: Keerthy K on Feb 26, 2009 6:06 AM