How to select a single entry with latest date just LE system date?

[Former Member]

Hi all,

Suppose there are 5 entries with first day (ertag) in table prow:

01.05.2005

01.06.2005

01.07.2005

01.08.2005

01.09.2005

Suppose system date (sy-datum) is 25.07.2005, I want a SQL that should select the third entry (01.07.2005).

FYI:

This SQL only selects the first entry:

Select single * from PROW into corresponding fields of ITAB where ERTAG LE SY-DATUM.

Could anyone help ? Thx.

[Former Member]

This select statement will be work:

Select single *

from PROW

into corresponding fields of ITAB

where ERTAG = ( select MAX( ERTAG ) from PROW where ERTAG LE sy-datum

).

But maybe a name confuse, the ITAB shouldn't a table, it should be a structure, as you only select one entry from PORW.

Thanks

[RichHeilman]

What is the logic for selecting the third entry? If you want the third entry in this case, you would have to select on that date.

Select Single * from PROW

into corresponding fields of itab

where ertag = '20050107'.

Also, you should probably be hitting this table with the pointer field as well.

Need to know more about your requirement.

Regards,

Rich Heilman

[Former Member]

Thanks.

I should not hardcode the date and should use SY-DATUM.

The logic is I want to get the entry which is of latest date from now.

My original failed SQL:

SELECT SINGLE PRWRT ERTAG

INTO CORRESPONDING FIELDS OF ITAB

FROM PROW

WHERE

ERTAG LE SY-DATUM

PNUM2 IN ( SELECT PNUM2 FROM PROP

WHERE HSNUM = '00'

AND PNUM1 IN

( SELECT PNUM1 FROM MAPR

WHERE WERKS = ITAB-WERKS

AND MATNR = ITAB-MATNR ) ).

[Former Member]

Hi, have you read my former reply, I'm sure it suit to you.

Base on your SQL statement, change it like this:

SELECT SINGLE PRWRT ERTAG

INTO CORRESPONDING FIELDS OF ITAB

FROM PROW

WHERE

ERTAG =

( select MAX( ERTAG ) from PROW where ERTAG LE sy-datum )

" the change for last date

AND " you lost it in here

PNUM2 IN ( SELECT PNUM2 FROM PROP

WHERE HSNUM = '00'

AND PNUM1 IN

( SELECT PNUM1 FROM MAPR

WHERE WERKS = ITAB-WERKS

AND MATNR = ITAB-MATNR ) ).

And again , the ITAB shouldn't be a table

thanks

[Former Member]

Hi,

This solves the problem:

SELECT MAX( ERTAG ) FROM PROW

INTO IT_EINE-ERTAG

WHERE ERTAG LE SY-DATUM.

SELECT SINGLE PRWRT

INTO CORRESPONDING FIELDS OF IT_EINE

FROM PROW

WHERE

ERTAG = IT_EINE-ERTAG

AND PNUM2 IN ( SELECT PNUM2 FROM PROP

WHERE HSNUM = '00'

AND PNUM1 IN

( SELECT PNUM1 FROM MAPR

WHERE WERKS = IT_EINE-WERKS

AND MATNR = IT_EINE-MATNR ) ).

[Former Member]

Of course, you can solve it in two step select.

But you can also combine them in one select, reference my former reply.

Thanks

[athavanraja]

another option.

data: first_day type sy-datum ,
      periv type T009B-PERIV ,
      poper type T009B-Poper ,
      year type T009B-BDATJ .



data: iprow type standard table of prow .

move: 'K4' to periv ,
      sy-datum+4(2) to poper ,
      sy-datum+0(4) to year .

CALL FUNCTION 'FIRST_DAY_IN_PERIOD_GET'
  EXPORTING
    i_gjahr              = year
*    I_MONMIT             = 00
    i_periv              = periv
    i_poper              = poper
 IMPORTING
   E_DATE               = first_day
 EXCEPTIONS
   INPUT_FALSE          = 1
   T009_NOTFOUND        = 2
   T009B_NOTFOUND       = 3
   OTHERS               = 4
          .
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.


select * from prow into table iprow where ertag between first_day and sy-datum .

Regards

Raja

[Former Member]

Should have been easier: In ABAP this works:

Select * from PROW into corresponding fields of ITAB

up to 1 rows

where ERTAG LE SY-DATUM

ORDER BY ERTAG descending.

ENDSELECT.

Cheers,

Ram