cancel
Showing results for 
Search instead for 
Did you mean: 

ALTER SEQUENCE in SQLScript stored procedure supported?

former_member182500
Contributor
0 Kudos

Hi,

I can successfully execute the following SQL statement within the Hana system SQL console:

alter sequence "MISSIONCONTROL"."hpl.missioncontrol.data::missionCockpitLogId" RESTART WITH 1;

I have an SQL stored procedure that I execute to clear some load tables as I repeatedly perform some tests:

CREATE PROCEDURE reset_missionLogs_sql ( )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER AS    

  --DEFAULT SCHEMA <schema>

BEGIN

/*****************************

  Write your procedure logic

*****************************/

  delete from "MISSIONCONTROL"."hpl.missioncontrol.data::mission";

  delete from "MISSIONCONTROL"."hpl.missioncontrol.data::missionCockpitLog";

 

END;

I wish to incorporate the aforementioned ALTER sequence statement within this procedure so that in addition to the table contents being cleared the associated sequences for primary keys are reset to 1.

The issue is that the SQLScript editor is not happy when I place the ALTER SEQUENCE statement, indicating it is "incorrect or or misplaced":

Is ALTER SEQUENCE supported?  Most grateful for pointing out the (probably) obvious to me.  Many thanks.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

I don't think you can do alter operation in the procedure. You can do insert/select/update/delete operations in the procedure because they can be rolled back when something goes wrong. But alter operation cannot be rolled back. Do everything or do nothing in one procedure. So you cannot truncate the table in the procedure due to the same reason.

Best regards,

Wenjun

former_member186082
Active Contributor
0 Kudos

Thanks Wenjun for your reply. Any work around that I can generate a sequence number based on a column of result set and populate along with the results of the same procedure?

Regards

Chandra.

former_member182302
Active Contributor
0 Kudos

Hi Chandra,

Can you share your scenario a bit more clearly?

Regards,

Krishna Tangudu

former_member186082
Active Contributor
0 Kudos

Hi Krishna,

Output of the SP is like this.

Column 1Column 2Column 3
C1V1C2V1C3V1
C1V2C2V2C3V2
C1V3C2V3C3V3
C1V4C2V4C3V2
C1V5C2V5C3V4
C1V6C2V6C3V1

I want to include one more field to output which gives sequence number for distinct values of column 3, i.e., output should be like this

Column 1Column 2Column 3Column 4
C1V1C2V1C3V11
C1V2C2V2C3V22
C1V3C2V3C3V33
C1V4C2V4C3V22
C1V5C2V5C3V44
C1V6C2V6C3V11

Result set of SP varies everytime it runs and hence column 4 has to be generated dynamically based on distinct column 3 values.

Please share your ideas on this.

Regards,

Chandra.

former_member186082
Active Contributor
0 Kudos

Wenjun, I'm able to reset sequence in SP which is not used in the same SP.

I'm not able to reset the same sequence which is used in the SP. Any thoughts?

Regards,

Chandu.

former_member182302
Active Contributor
0 Kudos

Hey Chandra,

Did you try using DISTINCT count on the 3rd column to fill the 4th column?

Regards,

Krishna Tangudu

former_member186082
Active Contributor
0 Kudos

Krishna,

That will give me distinct count of 3rd column but I cannot fill 4th column right...??


Regards
Chandu

Former Member
0 Kudos

Hi Chandra,

This should help you in achieving the desired result set. Try it out.

SELECT COLUMN1, COLUMN2, COLUMN3, DENSE_RANK() OVER (ORDER BY COLUMN3)

FROM TABLE_NAME;

Regards,

Akshay

former_member186082
Active Contributor
0 Kudos

Hi Akshay,

That worked perfectly as per my requirement. Thank you. Unfortunately, I cannot mark as correct answer as I'm not owning this thread.

Regards,

Chandu.

former_member182500
Contributor
0 Kudos

Any ideas, is there something obvious I am missing like a SQL command subset not available within SQLScript?

former_member186082
Active Contributor
0 Kudos

Hi Jon,

Were you able to achieve this? I want help on this, I'm stuck at same point.

Regards,

Chandra.