Short Dump CX_SY_OPEN_SQL_DB

[Former Member]

Hi experts,

While inserting data into a SE11 table, i am getting the following short dump.

Runtime Errors         SAPSQL_ARRAY_INSERT_DUPREC

Except.                CX_SY_OPEN_SQL_DB

Before inserting data, i am sorting my internal table and deleting adjacent duplicates by primary keys and secondary indexes.

Is there any workaround to fix my issue.

Thanks.

Amine

[nabheetscn]

Basically a data exists in database with the same key. so better first check if you want to update it if yes then please use Modify statement.

[Former Member]

Hi Nabheet,

To be more specific, here the syntax that i am using:

Sort internal table.

Delete adjacent duplicates from internal table comparing primary keys and secondary indexes

insert SE11table from table my internale table.

Do you see any thing wrong?

Amine

[Former Member]

does you table have any entries?

Its likely there is an entry already that matches the attempted insertion.

[former_member201275]

Is your table empty to start with? Maybe there is already an existing entry in the table and therefore you get this message?

[Former Member]

Hi Glenn,

My table is empty of course. I delete the data from it in my program before inserting it again.

Amine

[nabheetscn]

You are deleting the data from the Database table in your program i believe if that is the case after deleting did you do a Commit?

[former_member201275]

Please can you post your code, and provide a screenshot of your table.

[former_member201275]

I agree with a lot of the other responses in terms of using MODIFY command. This should do the trick. Have you tried this?

[Former Member]

Yes Nabheet.

[Former Member]

Here the code Glen:

SORT wt_report2 BY comp_code costcenter chrt_accts gl_account costelmnt /bic/y_tprtnr co_area profit_center y_labbu y_labdiv y_program part_prctr y_labprbu y_ptnrdiv doc_currcy loc_currcy funcarea fiscper version.

DELETE ADJACENT DUPLICATES FROM wt_report2 COMPARING comp_code costcenter chrt_accts gl_account costelmnt /bic/y_tprtnr co_area profit_center y_labbu y_labdiv y_program part_prctr y_labprbu y_ptnrdiv doc_currcy loc_currcy funcarea fiscper version.
*DELETE ADJACENT DUPLICATES FROM wt_report2 COMPARING all fields.
IF NOT wt_report2 IS INITIAL.
INSERT ycnv_report FROM TABLE wt_report2.
ENDIF.

The underlined fields are my secondary indexes. The other ones are my primary keys.

[Former Member]

Here an extract of my internal table in debug. Actually i have many empty records because i'm on a dev system, that might be the reason:

[Former Member]

Here the definition of my secondary indexes, tell me please if you see smthg wrong.

Thx

[RaymondGiuseppi]

You delete records having identical set of primary keys AND secondary keys (indexes), Do not, you must insure uniqueness for every unique index. Here you can keep two records with same primary keys if one secondary key differs.

So sort and delete comparing ONLY primary keys, then do the same for every index with unique key, but only with keys of one index for each deletion of duplicates.

Regards,

Raymond

[RaymondGiuseppi]

So you can only have a single record for one funcarea/fisper/version set ?

[Former Member]

Hi Raymond,

You mean i have to do this:

sort internal table by primary keys.

delete adjacent duplicates comparing primary keys.

sort internal table by secondary indexes.

delete adjacent duplcates comparing secondary indexes.

right?

thanks.

Amine

[RaymondGiuseppi]

[sivaganesh_krishnan]

HI Amine,

Check the SAP Notes : 196113 - SAPSQL_ARRAY_INSERT_DUPREC,table  BALHDR

Regards,

Sivaganesh

[snipersap]

Dear Amine,

Please take the data that you are trying to insert in a excel sheet.

Find the column which are primary key in your table and mark them in the excel sheet.

Copy the data and put them in the respective fields of the table in SE16.

If you find any output, then these are the entries which already exist in your table while you are trying to insert them via your program.

Solution:

Either correct your program or remove the found entries from the table before inserting them again from your program.

--

Regards

Soumyojyoti Pal

[Former Member]

Hi Pal,

The treatment has to be dynamic

Amine

[anupam_anand]

Hi Amine,

Try using MODIFY instead of INSERT. In case the entries are found, Modify will overwrite them.

Incase all the entries, already exist, you can also use Update.

Thanks,

Anupam

[Former Member]

I tried modify. It worked, i have no more short dump.

But the number of data in my se11 table is now false, i have only one record instead of 7728 ones.

May be i should decrease the number of keys?!

Amine

[anupam_anand]

Sort your internal table by only the primary keys. You shouldn't have duplicates there and again try to run your code.

Thanks,

Anupam

[RaymondGiuseppi]

Correct code for your indexes would be

* Primary index 
SORT wt_report2
   BY        comp_code costcenter chrt_accts gl_account costelmnt /bic/y_tprtnr co_area profit_center y_labbu
             y_labdiv y_program part_prctr y_labprbu y_ptnrdiv doc_currcy loc_currcy.
 DELETE ADJACENT DUPLICATES FROM wt_report2
   COMPARING comp_code costcenter chrt_accts gl_account costelmnt /bic/y_tprtnr co_area profit_center y_labbu
             y_labdiv y_program part_prctr y_labprbu y_ptnrdiv doc_currcy loc_currcy.
 * Secondary unique index
 SORT wt_report2
   BY        funcarea fiscper version.
 DELETE ADJACENT DUPLICATES FROM wt_report2
   COMPARING funcarea fiscper version.

So not many records are kept I suppose. Check your index definition.

Also I did not see the client MANDT in the index definition. So I suppose you did not add it to table definition, and were not able to define foreign key relations with standard tables for costcenter or gl_account fieds ?

Regards,

Raymond

[Former Member]

Thanks Raymond,

In fact i get only one record in the final table which is not correct

I don't need the MANDT since it's a custom table in BW and all records are coming from it.

Amine

[anupam_anand]

Did it work? I think it should work if you try to use Modify with the primary keys.

Thanks,

Anupam

[Former Member]

Hi Anupam,

Still not, i think that i may have an issue with the definition of my secondary indexes.

Please refer to the screenshot on top.

Thanks.

Amine

[anupam_anand]

Hi Amine,

May be weird but delete your secondary indexes and try to upload.

That way we can be sure of where the issue could be. There may be some rework but just trying to figure out the root of this.

Thanks,

Anupam

[anupam_anand]

Did you try this???

Thanks,

Anupam

[Former Member]

Hi,

This error is coming because you are trying to insert a record with the same key as of an existing record.

INSERT statement cannot overwrite the record if it already exists, use MODIFY statement.

regards,

Ashish Rawat

[Former Member]

Hi,

Inserting Duplicate records from internal table, will give you a dump if you have duplicate entries in the internal table.

Try this addition "ACCEPTING DUPLICATE KEYS" with your INSERT command, that will prompt sy-subrc = 4, instead of throwing a dump.

Definitely you have to be sure of entries in internal table as suggested by Raymond.

Cheers,

Sumit

[Former Member]

Hi Sumit,

Intersting, at least my data will be accepted. And the data will be agregated i guess if the same key already exists.

Amine

[anupam_anand]

Hi Amine,

Sy-subrc = 4 should again not update your table.

Thanks,

Anupam

[Former Member]

Hi Amine,

If sy-subrc = 4, then data will not be accepted to be updated in the table.

Cheers,

Sumit