topic Re: latest record from database table in Application Development and Automation Discussions

latest record from database table

Former Member — Tue, 20 Feb 2007 17:00:28 GMT

Hi all,

I am using select statement on a database table. The table contains records, and each one has an effective date.

effective dt record Rate

01/01/1800 R1 40

01/01/1800 R2 40

I want to select rates corresponding to R1 and R2. For R2, the rate is 40, but i need the rate 30 and not 40 for R1, because rate 30 has the most recent effective date. Please tell me if i can do this in the select query itself...

Please help me...

Thanks

Pranati.

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:12:12 GMT

Hi Pranati,

After selecting records in intenal table you can Sort them accrding to date using descending addition. loop the table & append first record into another internal table sing control breaks.

SORT itab BY effective_dt DESCENDING.

Ashvender

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:14:31 GMT

you can use the aggregate function MAX.

select max( date ) rate .. from <database table> into .. where ...

Regards,

Ravi

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:18:00 GMT

Hi Ashwender..

Thank you for your answer... but can we do this with in the select query .. can we avoid the internal table.. The reason is i am using this rates for another internal table(which has R1, R2 etc) which has thousands of records.. so i am worried about impact on performance if we use internal table.

Thanks

Pranati.

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:21:18 GMT

Hi,

Check this example.

This will get the latest record created date for the customer...from the table vbak..

DATA: BEGIN OF itab OCCURS 0,

kunnr TYPE kunnr,

date TYPE sydatum,

END OF itab.

SELECT kunnr MAX( erdat )

INTO TABLE itab

FROM vbak WHERE kunnr = 'ABC' OR kunnr = 'DEF'

GROUP by kunnr.

LOOP AT itab.

WRITE: / itab-kunnr, itab-date.

ENDLOOP.

You can change it accordingly for your requirement..

Thanks,

Naren

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:30:37 GMT

Hi Pranati,

If you want to avoid inernal table then you can for for code(use of MAX) suggested by Narenderan:

Ashvender

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:31:48 GMT

Hi Naren,

This is my select query..

SELECT max( zeff_dt ) bukrs zbrdn kostl hkont INTO TABLE l_t_brdncalc

FROM zfpbrdn

FOR ALL ENTRIES IN l_t_records

WHERE bukrs = l_t_records-bukrs.

zeff_dt is the effective date field. but this is giving me the following syntax error.

<b>The addition 'for all entries' excludes all aggregrate functions with the exception of

"count(*)", as the single element of the select clause.</b>

Is this possible with for all entries...(am i getting too greedy?!!)

Thanks

Pranati..

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:34:20 GMT

Hi,

I believe you cannot use for all entries and aggerate function together..

Instead you can build a range for the bukrs...and then use IN your select statement...

Also you need to give GROUP BY if you use MAX..

Thanks,

Naren

Message was edited by:

Narendran Muthukumaran

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:41:00 GMT

Hi Naren..

That sounds great... can u pls tell me about building range for bukrs...

Thanks

Pranati

Message was edited by:

pranati dalvi

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 17:44:23 GMT

Hi,

Try this..

Ranges: r_bukrs for bkpf-bukrs.

LOOP AT l_t_records.

Build the range.

r_bukrs-sign = 'I'.

r_bukrs-option = 'EQ'.

r_bukrs-low = l_t_records.

append r_bukrs.

ENDLOOP.

SORT r_bukrs by low.

DELETE adjacent duplicates from r_bukrs comparing low.

SELECT max( zeff_dt ) bukrs zbrdn kostl hkont INTO TABLE l_t_brdncalc

FROM zfpbrdn

WHERE bukrs IN r_bukrs

GROUP BY zbrdn. " I am not sure of the record field..But you have to give

" GROUP BY clause..give the correct field..

Hope this helps..

Thanks,

Naren

Re: latest record from database table

Former Member — Tue, 20 Feb 2007 22:24:40 GMT

Hi,

Please make sure to reward points for helpful answers..

Thanks,

Naren