topic Re: selecting the data from the table in Application Development and Automation Discussions

selecting the data from the table

Former Member — Fri, 14 Dec 2007 05:50:21 GMT

Hi,

I have a query where in the table a single customer is having many entries and all those entries has corresponding date on which it was created

My select statement has to get the most recent date out of it.

ex-

cus date

1 - 20.07.2007

2 - 13.06.2007

3 - 15.09.2007

then it should fetch 3 - 15.09.2007

I cannot use the MAX or any of the aggregate function as i am using FOR ALL ENTRIES statement.

Any pointers will be very helpful.

Please suggest the solution keeping the performance in mind as FOR ALL ENTRIES itab is having many customer data.

Regards

Kumar Shantanu

Re: selecting the data from the table

Former Member — Fri, 14 Dec 2007 05:55:28 GMT

Hi Kumar,

if u have begda and endda then in wher condition specify endda = 31.12.999 this wil fetch latest record..or else..

sort itab by date..

loop at itab.

at new customer.

append to itab1.

endat.

endloop.

Regards,

Kaveri

Re: selecting the data from the table

Former Member — Fri, 14 Dec 2007 05:57:11 GMT

suppose itab have two fileds cutomer n date...

<b>sort itab by customer number date descending.

delete adjacent duplicates from itab comparing customernumber.</b>

now only customernumber and corresponding latest dates will only be left....

if u want the entries for later use.. u can move it to diff internal table n do the same ..

Message was edited by:

Sheeba Bhaskaran

Re: selecting the data from the table

Former Member — Fri, 14 Dec 2007 05:58:20 GMT

Hi Kumar

You can fetch using ur select query and then sort the table by in descending order by date and then by your key field.

Then use the statement, delete adjacent duplicates, comaping your key field which will leave only rows which have recent date.

pseudo code:

<b>

SORT itab DESCENDING STABLE BY date key_field.

DELETE ADJACENT DUPLICATES FROM itab comparing key_filed.</b>

Cheers

Ravish

Re: selecting the data from the table

former_member386202 — Fri, 14 Dec 2007 05:58:25 GMT

Hi,

Usde order by statement

do like this

select f1 f2 f3 ...........

where date = s_date order by (date) descending

Regards,

Prashant

Re: selecting the data from the table

former_member194152 — Fri, 14 Dec 2007 05:59:37 GMT

hi,

Hi first select the data from database table to internal table using max function then using that internal table u can use FOR ALL ENTRIES clause.

Example.

data : zkna1 like kna1 occurs 0 with header line.

select * from kna1 into zkna1 where kunnr in s_kunnr and
                                                     <condition on date >.

* now use for all entry

select field1 field2 from <dbtab>into corresponding fields of table itab  for all entries in ZKNA1 were kunnr = zkna1-kunnr.

Rewards if helpful.

Regards

Gagan

Re: selecting the data from the table

Former Member — Fri, 14 Dec 2007 06:46:04 GMT

thanks sheeba for the answer but performance is not improved it is still fetching all the data from the table and then i am doing what you have send.

It would be better if i do the select query by restricting the date field there.

AS i have already told most of the people have answered using order by or something else(we cannot use these if the statement FOR ALL ENTRIES is there), and some by asking me to hit the database table twice

Please suggest answer by hitting the table only once.