topic Re: select coding in Application Development and Automation Discussions

select coding

Former Member — Mon, 17 Oct 2005 17:44:13 GMT

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

Re: select coding

Former Member — Mon, 17 Oct 2005 17:48:07 GMT

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

Re: select coding

RichHeilman — Mon, 17 Oct 2005 17:49:48 GMT

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

Re: select coding

RichHeilman — Mon, 17 Oct 2005 19:03:26 GMT

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

Re: select coding

Former Member — Mon, 17 Oct 2005 19:05:15 GMT

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

Re: select coding

RichHeilman — Mon, 17 Oct 2005 19:06:35 GMT

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

10001--~~20050403 <~~----- This

10001----20040403

10002----20060403

10002--~~20050403 <~~----- And This

10002----20040403

Regards,

Rich Heilman

Re: select coding

RichHeilman — Mon, 17 Oct 2005 19:13:28 GMT

If you want it like this.....

~~-id-date~~---

10001----20060403

10001--~~20050403 <~~----- This

10001----20040403

10002----20060403

10002--~~20050403 <~~----- And This

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

Re: select coding

Former Member — Mon, 17 Oct 2005 19:14:20 GMT

So do:


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

Rob

Re: select coding

RichHeilman — Mon, 17 Oct 2005 19:15:36 GMT

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

Re: select coding

Former Member — Mon, 17 Oct 2005 19:17:32 GMT

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.

Re: select coding

RichHeilman — Mon, 17 Oct 2005 19:20:53 GMT

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

Re: select coding

RichHeilman — Mon, 17 Oct 2005 19:35:01 GMT

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

Re: select coding

Former Member — Tue, 18 Oct 2005 01:40:28 GMT

Ravi,

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