topic Re: Selecting the immediate previous record from a table. in Application Development and Automation Discussions

Selecting the immediate previous record from a table.

jogeswararao_kavala — Tue, 11 Jun 2013 12:00:24 GMT

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

Re: Selecting the immediate previous record from a table.

Private_Member_49934 — Tue, 11 Jun 2013 12:24:39 GMT

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

Re: Selecting the immediate previous record from a table.

gurunathkumar_dadamu — Tue, 11 Jun 2013 12:44:34 GMT

Hi Jogeswara Rao,

Try the following code may help for your problem.

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

Re: Selecting the immediate previous record from a table.

Former Member — Tue, 11 Jun 2013 12:49:53 GMT

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.

Re: Selecting the immediate previous record from a table.

ThomasZloch — Tue, 11 Jun 2013 12:51:03 GMT

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

Re: Selecting the immediate previous record from a table.

Former Member — Tue, 11 Jun 2013 12:51:14 GMT

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

Re: Selecting the immediate previous record from a table.

jogeswararao_kavala — Tue, 11 Jun 2013 13:24:10 GMT

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

Re: Selecting the immediate previous record from a table.

jogeswararao_kavala — Tue, 11 Jun 2013 13:28:19 GMT

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

Re: Selecting the immediate previous record from a table.

jogeswararao_kavala — Tue, 11 Jun 2013 13:29:50 GMT

Thanks Ankit,

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

Jogeswara Rao K

Re: Selecting the immediate previous record from a table.

jogeswararao_kavala — Tue, 11 Jun 2013 13:34:10 GMT

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

Jogeswara Rao K

Re: Selecting the immediate previous record from a table.

RaymondGiuseppi — Tue, 11 Jun 2013 14:05:09 GMT

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

Re: Selecting the immediate previous record from a table.

Former Member — Tue, 11 Jun 2013 15:29:22 GMT

Please let us know the result.

BR,

Ankit

Re: Selecting the immediate previous record from a table.

Former Member — Tue, 11 Jun 2013 16:50:25 GMT

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.

Re: Selecting the immediate previous record from a table.

jogeswararao_kavala — Thu, 13 Jun 2013 03:37:05 GMT

Raymond's 'SELECT MAX' part worked.