topic Re: Insert into a DB table based on condition in Application Development and Automation Discussions

Insert into a DB table based on condition

Former Member — Thu, 21 May 2020 04:38:18 GMT

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!

Re: Insert into a DB table based on condition

matt — Thu, 21 May 2020 05:24:49 GMT

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.

Re: Insert into a DB table based on condition

former_member1716 — Thu, 21 May 2020 05:30:57 GMT

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.

Re: Insert into a DB table based on condition

MateuszAdamus — Thu, 21 May 2020 06:54:20 GMT

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

Re: Insert into a DB table based on condition

gasparerdelyi — Thu, 21 May 2020 20:39:38 GMT

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_MODIFICATION_8@8@
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_MODIFICATION_6@6@

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

Re: Insert into a DB table based on condition

Former Member — Fri, 22 May 2020 11:22:06 GMT

Thank you very much!

Re: Insert into a DB table based on condition

Former Member — Fri, 22 May 2020 11:22:35 GMT

Thank you!