Skip to Content
author's profile photo Former Member
Former Member

select coding

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    Posted on Oct 17, 2005 at 07:06 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2005 at 05:48 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Oct 17, 2005 at 05:49 PM
    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 17, 2005 at 07:35 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2005 at 01:40 AM

    Ravi,

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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.