Selecting the immediate previous record from a table.

[jogeswararao_kavala]

Dear Experts,

I have a requirement in code.

I need to select a single record from standard table VIQMEL

bearing ausvn (date) value, immediate before the so_ausvn-low value of select-option.

Hope I am understood !

Please help.

Jogeswara Rao K

[jogeswararao_kavala]

Dear Experts,

I have a requirement in code.

I need to select a single record from standard table VIQMEL

bearing ausvn (date) value, immediate before the so_ausvn-low value of select-option.

Hope I am understood !

Please help.

Jogeswara Rao K

[Private_Member_49934]

You may select all rows ( selected fields only) from VIQMEL where ausvn is < so_ausvn-low. in an internal table ( say it_viqmel)

sort it_viqmel by ausvn descending.

Read it_viqmel index 1.

Note : - This may not be best perfomance wise but it will work

[jogeswararao_kavala]

Thanks for reply Kumar,

Presently I am doing this only. But the issue is as the time passes on the number of records below the so_ausvn-low increase drastically, adversely influencing the performance. Hence this discussion.

Jogeswara Rao K

[gurunathkumar_dadamu]

Hi Jogeswara Rao,

Try the following code may help for your problem.

TABLES : viqmel.

TABLES : viqmel.

DATA : ls_select TYPE selopt,

        lt_select TYPE TABLE OF selopt.

DATA : lv_viqmel TYPE viqmel.

DATA : lv_date1 TYPE sy-datum,

        lv_date2 TYPE sy-datum.

SELECT-OPTIONS :  s_ausvn FOR viqmel-ausvn.

START-OF-SELECTION.

   LOOP AT s_ausvn.

     lv_date1 = s_ausvn-low - 1.

     ls_select-low = lv_date1.

     lv_date2 = s_ausvn-high.

     ls_select-high = lv_date2.

     ls_select-sign = s_ausvn-sign.

     ls_select-option = s_ausvn-option.

     APPEND ls_select TO lt_select.

   ENDLOOP.

   SELECT SINGLE FROM viqmel INTO lv_viqmel WHERE ausvn IN lt_select.

If yoy have any issue let me know.

Regards,

Gurunath

[Former Member]

Hi Jogeswara,

One suggestion if you can then get the data from table QMIH instead of view VIQMEL.

Also try using select single with order by clause.

(Order By - http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a53358411d1829f0000e829fbfe/content.htm )

I think it should work but since i am away from SAP right now i cannot try.

BR,

Ankit.

[jogeswararao_kavala]

Thanks Ankit,

I am too of the system presently, This idea might help. I'll try this.

Jogeswara Rao K

[Former Member]

Please let us know the result.

BR,

Ankit

[jogeswararao_kavala]

Raymond's 'SELECT MAX' part worked.

[ThomasZloch]

As this is a date field, there might well be several records in VIQMEL with the same "immediate previous" value in AUSVN. On which additional basis do you want to determine a single record from this result set? AUZTV?

Look at the additions UP TO x ROWS and ORDER BY of the SELECT statement to find a solution.

Thomas

[jogeswararao_kavala]

You are right Thomas, in predicting the additional field. For simplicity of idea, I've skipped this field.

Thans for the suggestion, I shall try when I get back to system.

Jogeswara Rao K

[Former Member]

Hi,

   Try this one..

SELECT field1
       field2
       field3
  FROM VIQMEL INTO TABLE <INTERNAL_TABLE>
    UP TO 1 ROWS
WHERE AUSVN LT so_ausvn-low
ORDER BY AUSVN descending.

READ TABLE <INTERNAL_TABLE> INDEX 1.

Additional, you can consider creation date & time in descending order in where clause.

Regards,

Mordhwaj

[jogeswararao_kavala]

'UPTO ONE ROWS' syntax, I'll try this. Thanks Mordhwaj!.

Jogeswara Rao K

[RaymondGiuseppi]

Some tricks available (many other exist)

DATA: lv_qmnum TYPE viqmel-qmnum,
       lv_ausvn TYPE viqmel-ausvn.
 
 SELECT-OPTIONS so_ausvn FOR viqmel-ausvn NO INTERVALS.
 
 START-OF-SELECTION.
 
   GET TIME.
   CLEAR lv_qmnum.
   WRITE: / sy-uzeit, lv_qmnum, so_ausvn-low.
 * With order by
   SELECT qmnum ausvn INTO (lv_qmnum, lv_ausvn)
     FROM viqmel UP TO 1 ROWS
     WHERE ausvn LT so_ausvn-low
     ORDER BY ausvn DESCENDING.
   ENDSELECT.
   GET TIME.
   WRITE: / sy-uzeit, lv_qmnum, lv_ausvn.
 * 2 slects
   SELECT MAX( ausvn ) INTO lv_ausvn
     FROM viqmel UP TO 1 ROWS
     WHERE ausvn LT so_ausvn-low.
   SELECT qmnum INTO lv_qmnum
     FROM viqmel UP TO 1 ROWS
     WHERE ausvn EQ lv_ausvn.
   ENDSELECT.
   GET TIME.
   WRITE: / sy-uzeit, lv_qmnum, lv_ausvn. 
 * subquery
   SELECT qmnum ausvn INTO (lv_qmnum, lv_ausvn)
     FROM viqmel UP TO 1 ROWS
       WHERE ausvn EQ ( SELECT MAX( ausvn ) FROM viqmel WHERE ausvn LT so_ausvn-low ).
   ENDSELECT.
   GET TIME.
   WRITE: / sy-uzeit, lv_qmnum, lv_ausvn.

Regards,

Raymond

[Former Member]

Hi Jogeswar Rao,

TABLES viqmel.

TYPES: BEGIN OF ty_ausvn,

        ausvn TYPE viqmel-ausvn,

   END OF ty_ausvn.

DATA:lv_ausvn TYPE viqmel-ausvn,

      ls_ausvn TYPE ty_ausvn,

      lt_ausvn TYPE STANDARD TABLE OF ty_ausvn.

SELECT-OPTIONS so_ausvn FOR viqmel-ausvn.

SELECT ausvn FROM viqmel INTO TABLE lt_ausvn WHERE ausvn NE 0.

SORT lt_ausvn.

CLEAR ls_ausvn.

LOOP AT lt_ausvn INTO ls_ausvn WHERE ausvn LT so_ausvn-low.

   IF ls_ausvn IS NOT INITIAL.

     CONTINUE.

   ENDIF.

ENDLOOP.

WRITE:ls_ausvn-ausvn.