Insert into a DB table based on condition

[Former Member]

Hi!
I have a selection screen that insrts into a DB table. it has two options- enter an entry with start time, and then enter an entry with end time. is it possible to create a logic that when someone creats an entry with end time, it will insert to the same table row of the start time, just the end time?
I need it to insert into the last entry in X table where y=y (one of the fields needs to be eq on both entries).
and then define which fields will be inserted where.

maybe someone has an idea how to achive that?

Thank you!

[matt]

In a relational database there is no concept of order. Therefore, unless you build it in, there is no "last entry".

It would be helpful if you give some example of what you're trying to achieve, i.e. what's in the table before, what action you take and what you want in the table afterwards.

[former_member1716]

userc,

I can understand that you are trying to log the changes but could not get the complete business requirement, if you could help us an example it will be better to suggest a solution.

[MateuszAdamus]

Hi userc

Maybe something like this?

PARAMETERS: p_y TYPE char10 OBLIGATORY.
PARAMETERS: p_start TYPE dats.
PARAMETERS: p_end TYPE dats.

START-OF-SELECTION.
  PERFORM run_report.
  COMMIT WORK AND WAIT.

FORM run_report.
  IF p_start IS INITIAL AND p_end IS INITIAL.
    WRITE: / 'Please provide Start or End date'.
    RETURN. " nothing to do
  ENDIF.

  SELECT *
    FROM ztable
    WHERE y = @p_y
    ORDER BY record_count DESC " I assume there is some record counting column
    INTO @DATA(ls_ztable)
    UP TO 1 ROWS.
  IF sy-subrc = 0.
    IF p_start IS NOT INITIAL.
      IF ls_ztable-end_date IS INITIAL.
        WRITE: / 'You should provide End date for the last record'.
        RETURN.
      ELSE.
        " last record is closed
        PERFORM create_record USING ls_ztable-record_count.
      ENDIF.

      RETURN.
    ENDIF.

    IF p_end IS NOT INITIAL.
      IF ls_ztable-end_date IS NOT INITIAL.
        WRITE: / 'Last record already closed. Create new one'.
        RETURN.
      ELSE.
        ls_ztable-end_date = p_end.
        MODIFY ztable FROM ls_ztable.
      ENDIF.
    ENDIF.
  ELSE.
    " no last record found
    " create new but only if Start date provided
    IF p_start IS NOT INITIAL.
      PERFORM create_record USING 0.
    ELSE.
      WRITE: / 'No last record found'.
    ENDIF.
  ENDIF.
ENDFORM.

FORM create_record USING iv_record_count TYPE i.
  DATA:
    ls_ztable TYPE ztable.

  CHECK p_start IS NOT INITIAL.

  ls_ztable-y = p_y.
  ls_ztable-record_count = iv_record_count + 1.
  ls_ztable-start_date = p_start.
  INSERT INTO ztable FROM ls_ztable.
ENDFORM.

My assumption is that there is some record counting key column in the table.

It my be a simple integer column, a numc or a date one. You have to have a look and modify the code respecitvely.

Regards,

Mateusz

[Former Member]

Thank you very much!

[gasparerdelyi]

There is a very simple way to do that within a single OpenSQL statement since ABAP Release 7.52 (also in S/4HANA OP 1709).

Since 7.50, you can use a subquery as a data source for an INSERT statement in OpenSQL:
https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abennews-750-open_sql.htm#!ABAP_MODIFICAT...
Since 7.52 you can use the ORDER BY clause and the UP TO addition within the subquery: https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abennews-752-open_sql.htm#!ABAP_MODIFICAT...

These two changes altogether allow exactly what you are looking for -- without transferring the records from database to application server and transferring them back.

[Former Member]

Thank you!