select coding

[Former Member]

hi,

i have several entries in db as follows.

-id-date---

10001----20060403

10001--20050403 <-----

10001----20040403

i need to pick the record which is less than or equal to current date and the most current one. in this case i need to pick the second record.

i DONT WANT TO USE internal table. could some one suggest a select statement on which i can proceed.

your help would be appreciated

Thanks,

ravi.

rob and rich,

one small problem, i have several records with different id's but if i use this its working only for one id, can you suggest some modifications so that i can go ahead.its important for me to use select and endselect.

Thanks,

ravi.

rich,

i need one record per id with the above validations(there are several fields i need to select apart from id and date).

Thanks,

ravi.

Message was edited by: ravi kumar

Message was edited by: ravi kumar

[Former Member]

By the way, I'm curious, why is it important for you to use SELECT..ENDSELECT. Why are you so against internal tables. You know that SELECT...ENDSELECT is slower than an Array Fetch(internal table), right?

Regards,

Rich Heilman

[Former Member]

Try:

select ...
up to 1 rows
where date le sy-datum
order by db_date_field descending

Rob

I changed this a bit.

Message was edited by: Rob Burbank

[Former Member]

You can get rid of the "up to 1 rows", but you will select more than 1 record. So you'll probably want to use an internal table to decide which id to use.

Rob

[Former Member]

So do:

select ...
where date le sy-datum
order by db_date_field descending
group by date id

Rob

[Former Member]

SELECT (your_field_list_here) up to 1 rows

WHERE the_date_field le sy-datum

order by the_date_field descending

group by id.

• your business logic here

ENDSELECT.

[RichHeilman]

If you need more fields from the db, then just add them to the internal table...

data: begin of itab occurs 0, 
      userid(10) type c,     
      date type sy-datum,  
<b>      fld1(10) type c,
      fld2(10) type c,
      fld3(10) type c,</b>
      end of itab.


Select * <b>into corresponding fields of table itab</b> 
           from zdb           
           where   date <= sy-datum      
             order by date descending.

sort itab by userid ascending date descending.

delete adjacent duplicates from itab comparing userid.

Regards,

Rich Heilman

[RichHeilman]

Select Single * into structure
      from zdb
         where date <= sy-datum order by date.

I don't know if this is the correct syntax. But I believe it will work if you use the order by extension.

Regards,

Rich Heilman

[RichHeilman]

Do you need to select for a specific id? If so, then just add it to the WHERE clause of the select statement.

Select Single * into structure     
     from zdb         
      where  userid = '1001'
         and date <= sy-datum 
               order by date descending.

Regards,

Rich Heilman

[RichHeilman]

Or do you want to get 1 record per userid. The record which date <= sy-datum and the closes to today.

What I mean is.... do you want?

-id-date---

10001----20060403

<b>10001--20050403 <----- This</b>

10001----20040403

10002----20060403

<b>10002--20050403 <----- And This</b>

10002----20040403

Regards,

Rich Heilman

[RichHeilman]

If you want it like this.....

-id-date---

10001----20060403

<b>10001--20050403 <----- This</b>

10001----20040403

10002----20060403

<b>10002--20050403 <----- And This</b>

10002----20040403

You will have to use an internal table.

data: begin of itab occurs 0,
      userid(10) type c,
      date type sy-datum,
      end of itab.

Select * into table itab  
         from zdb            
        where      
           date <= sy-datum      
             order by date descending.

sort itab by userid ascending
             date   descending.

delete adjacent duplicates from itab comparing userid.

This should work for ya.

Regards,

Rich Heilman

[RichHeilman]

Please don't change the original post, rather come back to use by posting another reply to this thread. Thanks. My previous answer should help you. You must use an internal table, though, because you want multiple records.

Regards,

Rich Heilman

[RichHeilman]

By the way, I'm curious, why is it important for you to use SELECT..ENDSELECT. Why are you so against internal tables. You know that SELECT...ENDSELECT is slower than an Array Fetch(internal table), right?

Regards,

Rich Heilman

[Former Member]

Ravi,

Is your issue resolved? If so, please reward points accordingly.