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

Select....Query...

select single * from zcta_pp_spncap

where kapname eq kakt-ktext

and matnr eq afko-plnbez

and fdate eq

( select max( fdate ) from zcta_pp_spncap

where kapname eq kakt-ktext

and matnr eq afko-plnbez

and fdate <= git_afru-budat ).

In the above select query, i need to take the values from table zcta_pp_spncap. If the given date git_afru-budat (DATE) not matches, system should pick the latest next date.

Ex: Given date :git_afru-budat - 01.08.2006

if entry not available for the above date, system should pick the latest date, ie., 30.07.2006 (or) where the datas available.

For which i used the above expression, but sy-subrc returns 4.

Add comment
10|10000 characters needed characters exceeded

4 Answers

  • Posted on Aug 01, 2006 at 09:49 AM

    Hello,

    First get the max date,

    select max( fdate ) from zcta_pp_spncap

    into <b>l_date</b>where kapname eq kakt-ktext

    and matnr eq afko-plnbez

    and fdate <= git_afru-budat.

    and then

    select single * from zcta_pp_spncap

    where kapname eq kakt-ktext

    and matnr eq afko-plnbez

    and fdate eq l_date

    regards,

    Naimesh

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 01, 2006 at 09:54 AM

    hi do like this:

    select single * from zcta_pp_spncap

    where kapname eq kakt-ktext

    and matnr eq afko-plnbez

    and fdate eq git_afru-budat.

    if sy-subrc <> 0.

    select max( fdate ) from zcta_pp_spncap

    into v_date where kapname eq kakt-ktext

    and matnr eq afko-plnbez

    and fdate <= git_afru-budat.

    if sy-subrc = 0.

    select single * from zcta_pp_spncap

    where kapname eq kakt-ktext

    and matnr eq afko-plnbez

    and fdate eq v_date.

    endif.

    endif.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 01, 2006 at 09:56 AM

    Hi Ravi,

    first select the latest date from table and then you can use that value to select the desired record.

    Select MAX( fdate ) from zcta_pp_spncap

    Into Latest_date

    where kapname eq kakt-ktext

    and matnr eq afko-plnbez

    and fdate <= git_afru-budat .

    Select single *

    from zcta_pp_spncap

    where kapname eq kakt-ktext

    and matnr eq afko-plnbez

    and fdate eq git_afru-budat .

    If sy-subrc NE 0.

    Select single *

    from zcta_pp_spncap

    where kapname eq kakt-ktext

    and matnr eq afko-plnbez

    and fdate eq Latest_date.

    Endif.

    Hope this helps,

    Pragya

    .

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 01, 2006 at 04:02 PM

    Hi Ravi,

    Your query seems to be correct. I have created a sample query and tested. Its working fine.

    My suggestion is first check the sub query whether it is fetching correct date ot not.

    Also check the dates (which you are comparing in the where clause of the subquery) are of same type.

    Regards,

    Suresh P

    Add comment
    10|10000 characters needed characters exceeded