topic Re: Select record which is valid for current date in Application Development and Automation Discussions

Select record which is valid for current date

Former Member — Mon, 07 May 2012 19:44:10 GMT

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

Re: Select record which is valid for current date

former_member189779 — Mon, 07 May 2012 19:49:53 GMT

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

ZTAB

INTO L_ZHYPENT WHERE RCOMP = I_KNA1-VBUND

Re: Select record which is valid for current date

Former Member — Mon, 07 May 2012 19:51:13 GMT

My table key are RCOMP and EFF_TO

Re: Select record which is valid for current date

Former Member — Mon, 07 May 2012 19:52:31 GMT

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

Re: Select record which is valid for current date

Former Member — Mon, 07 May 2012 19:55:09 GMT

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

Re: Select record which is valid for current date

Former Member — Mon, 07 May 2012 19:59:19 GMT

Nonono

Add the date selection to the WHERE.

Rob

Re: Select record which is valid for current date

Former Member — Mon, 07 May 2012 20:09:11 GMT

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.

Re: Select record which is valid for current date

former_member189779 — Mon, 07 May 2012 20:12:43 GMT

Try remove order by.

SELECT * UP TO 1 ROWS FROM ZTAB

INTO L_ZHYPENT WHERE RCOMP = I_KNA1-VBUND
ENDSELECT.

Re: Select record which is valid for current date

Former Member — Mon, 07 May 2012 20:30:37 GMT

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.