Select record which is valid for current date

[Former Member]

Hi Experts,

I am writing the below logic to check if the record is valid or not:

SELECT * UP TO 1 ROWS FROM ZTAB

INTO L_ZHYPENT WHERE RCOMP = I_KNA1-VBUND
ORDER BY EFF_TO DESCENDING  .
ENDSELECT.
IF SY-SUBRC = 0.
IF SY-DATUM LT L_ZHYPENT-EFF_FROM OR SY-DATUM > L_ZHYPENT-EFF_TO.
MESSAGE TEXT-011 TYPE 'E'.
ENDIF.

But the above logic will pick the second entry which will be invalid. I want to select record which is valid for current date.

Any suggestions on this?

Database:

RCOMP    EFF_TO     EFF_FROM      

109035     2012012     2011006

109035     9999012     2013001

[former_member189779]

if RCOMP is complete key of your table. Try Select Single * from

ZTAB

INTO L_ZHYPENT WHERE RCOMP = I_KNA1-VBUND

[Former Member]

My table key are RCOMP and  EFF_TO

[former_member189779]

Try remove order by.

SELECT * UP TO 1 ROWS FROM ZTAB

INTO L_ZHYPENT WHERE RCOMP = I_KNA1-VBUND
ENDSELECT.

[Former Member]

You need to check that today's date is greater than the effective date and less than the end date.

Forget about up to n rows and order by.

Rob

[Former Member]

Hi Rob,

So should i select all the data in my table and then check the dates?

Becuase if there is no data in the ztable for the condition i dont have to display the error message

[Former Member]

Nonono

Add the date selection to the WHERE.

Rob

[Former Member]

If i add date selection in where clause and there is no entry in the table then also there will be a error message which will be incorrect:

SELECT * UP TO 1 ROWS FROM ZTAB

INTO L_ZHYPENT WHERE RCOMP = I_KNA1-VBUND
                                         and EFF_TO GE sy-datum  .
ENDSELECT.
IF SY-SUBRC <> 0 . "even if there is no entry in ZTAB for the conditionit will display error message
MESSAGE TEXT-011 TYPE 'E'.
ENDIF.

[Former Member]

This is how i made it work but i dnt like loop...endloop 😞

    SELECT *  FROM ZHYPENT
      INTO  TABLE LT_ZHYPENT
      WHERE RCOMP = I_LFA1-VBUND.
*--end of RD1K920476
     IF SY-SUBRC = 0.
       LOOP AT LT_ZHYPENT INTO LW_ZHYPENT WHERE EFF_FROM LE SY-DATUM
                                          AND   EFF_TO   GE SY-DATUM.
       ENDLOOP.
       IF SY-SUBRC <> 0.
       MESSAGE TEXT-011 TYPE 'E'.
      ENDIF.
     ENDIF.