Skip to Content
0

Error handling in HANA stored proedure

Feb 15, 2017 at 09:17 AM

57

avatar image

Hi Expert,

We have a requirement where we need store the error records in a custom table with the entire records details.

For an example , I have a custom table with 15 rows out of them 5 are good records and 3 are bad records. I need to store this 3 bad records in another custom table .

We are using mass insert option the stored procedure . Is there any way to achieve this without using loop in store procedure?

Please suggest.

Thanks,

Girdhari

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Lucas Oliveira
Feb 16, 2017 at 02:43 AM
0

Hi Girdhari,

Difficult to understand what exactly you want to do here. Please elaborate on what you meant by "mass insert option" and provide a shorter version of your SP along with data for the source table and target table.

Regards,

Lucas de Oliveira

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Lukas,

Thanks for your reply.

We are using below statement to insert records from a calculation view into a physical table.

insert into SAURAVSA.PRODUCT_NEW (Select * from "_SYS_BIC"."******.******.******/CV_PRODUCT")

Now our requirement is if calculation view has 10 records and out of those 3 records has some error. We need to store the 3 error records in another error table with error message and remaining 7 success records needs to be updated in table PRODUCT_NEW.

I did some research and understand that , we have to use cursor or for loop to achieve this requirement but cursor and loop is not recommended in our project. Is there any other way to achieve this without using cursor or for loop?

Thanks,

Girdhari

0

Hi,

You may try something like this as split the "_SYS_BIC"."******.******.******/CV_PRODUCT as per your condition using where condition in to separate internal temp tables.

as Good_data = Select * from "_SYS_BIC"."******.******.******/CV_PRODUCT"

where column = condition

bad_data = Select * from "_SYS_BIC"."******.******.******/CV_PRODUCT" where not (column = condition)

And then use the good data in to existing to the

insert into SAURAVSA.PRODUCT_NEW (Select * from Good_data)

insert into SAURAVSA.PRODUCT_NEW_BAD (Select * from bad_data)

Let know how it goes.

0

Hi Srikanth,

Thanks for reply.

We can't split the data before inserting the data into physical data.

I mean to say if the some records will not gets inserted in table due to some error , then those erroneous records needs to be gets updated in error log table.

Thanks,

Girdhari

0
Lucas Oliveira
Feb 16, 2017 at 09:19 PM
0

Hi,

That's still not clear Girdhari. The recommendation from Srikanth makes sense so please explain with details with you can't have intermediate result (for good and bad data) before inserting. So far I can't see why you'd need cursor here.

Regards,

Lucas de Oliveira

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Lukas,

Let me try to explain you the scenario .Suppose calculation view has 100 records but while inserting in physical table , insert got failed due to some error in some records (Like duplicate entry,data type mismatch etc). As per below insert statement complete insert will get failed and none of of the records gets inserted in the table, correct me if I am wrong.

insert into SAURAVSA.PRODUCT_NEW (Select * from "_SYS_BIC"."******.******.******/CV_PRODUCT")

But our requirement is not to fail the complete insert , the records which do not have any error should get inserted in the physical table and the records which has error should get inserted in another error table. But as per my understanding this can be only done using Loop which is not recommenced. Is there any other way to do this without using Loop? Hope I am able to explain you the scenario .

Thanks,

Girdhari

0