topic Re: Insert or Modify statement from internal table to database table in Application Development and Automation Discussions

Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 16:50:41 GMT

Hi All,

I have three tables wakh, wrf and wakp. I have an internal table with 5 columns col1, col2, col3, col4 and col5.

The value in Col1 is my article no and the articleno. is in the table wakh. The value in col2 is my ccode and it is in the table wrf. The rest three columns col3, col4 and col5 are unit, qty and price and they are in the wakp table. Now when my articleno is equal to ccode I need to update the col3, col4 and col5 values in the wakp. wakp has around 20 columns.

Can anyone of you guys please give me the code for this issue. Your help is highly appreciated and thanks for all for your time.

Cheers,

Cheng

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 16:56:46 GMT

hi,

do this way ... assuming that the col3, col4, col5 are being written to

it_wakh table with all the fields present in it ...


loop at it_wakh.
  lv_tabix = sy-tabix.
  read table it_wrf with key col2 = it_wakh-col1.
  if sy-subrc = 0.
   modify wakp from it_wakh transporting  col3 col4 col5 index lv_tabix .
 endif.
endloop.

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 17:09:12 GMT

Hi Santosh,

Thanks for replying to me. I forget to tell in my last thread that my internal table is wa_datatab. And also just to tell you that I am currently uploading excel to internal table.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3,

wa_datatab-col4,

wa_datatab-col5.

ENDLOOP.

Currently I am displaying the internal table. So now instead of displaying I have to send the values into the database table. I hope this will give you some idea what I am doing. Can you please give me the code accordingly. Thanks for your time.

Cheng.

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 17:17:36 GMT

hi,

do this way ..Here i have assumed that you are trying to modify AKTNR

ARTNR

MEBME fields of WAKP table ...

data : wa_wakp type wakp.


LOOP AT it_datatab INTO wa_datatab.
  if wa_datatab-col1 = wa_datatab-col2.
    wa_wakp-AKTNR = wa_datatab-col3.
    wa_wakp-ARTNR = wa_datatab-col4.
    wa_wakp-MEBME = wa_datatab-col5.
    modify wakp from wa_wakp.
  endif.
ENDLOOP.

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 17:23:34 GMT

Mmm, have you seen if there is a bapi to update those tables?

Is not recomended to update or insert in standard tables just like that. It's likely that you will end up breaking something

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 17:28:03 GMT

Hi Santosh,

In the code what you have given in the second line:

if wa_datatab-col1 = wa_datatab-col2.

Here col1 value is articleno and it is in table wakh

and col2 value is ccode and it is inthe table wrf

So we should compare the values which in these columns with the corresponding article and ccode and then update rest of the three fields. sorry for my ignorance if I am not on the same page with you.

Cheng.

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 17:29:41 GMT

Ramiro,

I am new to ABAP and also I am not sure of any bapi whether exists for my requirement. If you know can you please let me know.

Cheng

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 17:42:46 GMT

Looks like you could make a bdc to transaction wak2 but I don't have data here to check if you can modify the header fields.

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 17:45:14 GMT

If you are new to ABAP, then you should definitely be looking for a standard way to do this (BAPI or BDC).

What transaction would you functional person use to update this?

Rob

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 18:12:48 GMT

Hi Rob,

We will use wak2. Can you please guide me properly.

Cheers,

Cheng.

Edited by: Cheng Wan on Jun 17, 2008 8:15 PM

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 18:16:10 GMT

Try BAPI_PROMO_CHANGE.

Rob

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 18:29:07 GMT

Hi Rob,

let me explain you the whole process what i am trying to do. I have a screen where there are 3 fields. In my first field I have a promoiton no. As soon as the user enters the promotion no. its description will be populated in my second field. If the promotion is not maintained then it will throw an error. In my third field User will upload an excel sheet which has 5 columns articleno, colorcode, salesunit, qty, mdprice. Here articleno is coming from wakh and colorcode is in wrf_charval table and the rest three fields are coming from wakp table. So for the article no. which is in col1. and for its corresponding colorcode which is in col3 i need to update col3, col4, col5 values.

With my below code I am able to upload the excel into internal table and display it. So instead of displaying I need to update in the database. Can you please let me know how I need to attach the function module within my code and update accordingly.

REPORT ZTest.

tables : wakh, wakt.

Parameter: PromoID type wakh-aktnr, PromoDec type wakt-aktkt, p_file LIKE rlgrap-filename

DEFAULT 'c:\test.xls' OBLIGATORY. " File Name

*FileName type string.

*file_nm type localfile.

TYPES: BEGIN OF t_datatab,

col1(25) TYPE c,

col2(30) TYPE c,

col3(30) TYPE c,

col4(30) TYPE c,

col5(30) TYPE c,

END OF t_datatab.

DATA: it_datatab TYPE STANDARD TABLE OF t_datatab INITIAL SIZE 0,

wa_datatab TYPE t_datatab.

Data : p_table type t_datatab occurs 0 with header line.

DATA : gd_scol TYPE i VALUE '1',

gd_srow TYPE i VALUE '2',

gd_ecol TYPE i VALUE '5',

gd_erow TYPE i VALUE '65536'.

DATA: it_tab TYPE filetable,

gd_subrc TYPE i.

field-symbols : <fs>.

AT selection-screen on PromoID.

select single * from wakh where aktnr = PromoID.

if sy-subrc EQ 0.

select aktkt from wakt into PromoDec where aktnr eq PromoID.

endselect.

else.

message A000(ZI) with 'Promotion ID is not Maintained.'.

endif.

*Title : Excel Uploading

***************************************************************

*********************************************************************

*Selection screen definition

*SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

*PARAMETERS: p_file LIKE rlgrap-filename

DEFAULT 'c:\test.xls' OBLIGATORY. " File Name

*SELECTION-SCREEN END OF BLOCK b1.

***********************************************************************

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

REFRESH: it_tab.

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title = 'Select File'

default_filename = '*.xls'

multiselection = ' '

CHANGING

file_table = it_tab

rc = gd_subrc.

************************************************************************

LOOP AT it_tab INTO p_file.

so_fpath-sign = 'I'.
so_fpath-option = 'EQ'.
append so_fpath.

ENDLOOP.

***********************************************************************

START-OF-SELECTION.

PERFORM upload_excel_file TABLES it_datatab

USING p_file

gd_scol

gd_srow

gd_ecol

gd_erow.

***********************************************************************

END-OF-SELECTION.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3,

wa_datatab-col4,

wa_datatab-col5.

ENDLOOP.

&----

*& Form UPLOAD_EXCEL_FILE

&----

upload excel spreadsheet into internal table

----

-->P_TABLE Table to return excel data into
-->P_FILE file name and path
-->P_SCOL start column
-->P_SROW start row
-->P_ECOL end column
-->P_EROW end row

----

FORM upload_excel_file TABLES p_table

USING p_file

p_scol

p_srow

p_ecol

p_erow.

DATA : lt_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.

Has the following format:
Row number | Colum Number | Value
---------------------------------------
i.e. 1 1 Name1
2 1 Joe

DATA : ld_index TYPE i.

Note: Alternative function module - 'ALSM_EXCEL_TO_INTERNAL_TABLE'

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_file

i_begin_col = p_scol

i_begin_row = p_srow

i_end_col = p_ecol

i_end_row = p_erow

TABLES

intern = LT_INTERN

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE:/ 'Error Uploading file'.

EXIT.

ENDIF.

IF lt_intern[] IS INITIAL.

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE:/ 'No Data Uploaded'.

EXIT.

ELSE.

SORT lt_intern BY row col.

LOOP AT lt_intern.

MOVE lt_intern-col TO ld_index.

assign component ld_index of structure

p_table to <fs>.

move : lt_intern-value to <fs>.

MOVE lt_intern-value TO p_table.

AT END OF row.

APPEND p_table.

CLEAR p_table.

ENDAT.

ENDLOOP.

ENDIF.

ENDFORM.

Thanks for your valuable time.

Cheng

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 18:34:03 GMT

The BAPI is documented. You should check that first. If you still have questions, get back to the forum.

Rob

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 18:44:57 GMT

Hi Rob,

What I understand from you and documentation is I need to pass the parameters (values from my internal table) to the function module. Then the function module will call and executes and updates.

But i am not sure whether it updates with the article no. and color code or not. I know you guys are master in this and would have done similiar kind of dev earlier. Since I am not sure of how to pass my internal table data to the funciton module can you please shed some light on this.

Cheng.

Re: Insert or Modify statement from internal table to database table

Former Member — Tue, 17 Jun 2008 20:30:03 GMT

I've never used this BAPI.

In general, what I would do though, is to play around with it and see what it does. If you don't do a COMMIT afterwards, things tend not to be updated, so you can't do much damage.

Rob