SQL statement using variable got error on HANA

[former_member611865]

Hi experts,

I am trying to write SQL statement as below, that gets the backup ID of latest complete data backup and delete backups before the backup ID.

---------------------------------------------------------------------------------------
DO
BEGIN
DECLARE Y_BACKUP_ID BIGINT;
SELECT BACKUP_ID INTO Y_BACKUP_ID FROM M_BACKUP_CATALOG WHERE SYS_START_TIME = (SELECT max(SYS_START_TIME) FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME = 'complete data backup');
BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID :Y_BACKUP_ID COMPLETE;
END;
---------------------------------------------------------------------------------------

However, I got an error below.
Could anyone tell me where is the problem with this SQL statement?

---------------------------------------------------------------------------------------
Could not execute 'DO BEGIN DECLARE Y_BACKUP_ID BIGINT; SELECT BACKUP_ID INTO Y_BACKUP_ID FROM M_BACKUP_CATALOG WHERE ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ":Y_BACKUP_ID": line 5 col 45 (at pos 267)
---------------------------------------------------------------------------------------

Thank you.

[gopinath_kolli]

You are doing a select query which returns a table result and assigning it to a variable which holds a single value.

Do like this. (make sure that the select query returns single value to avoid aggregation)

DO
BEGIN
DECLARE Y_BACKUP_ID BIGINT; 


SELECT SUM(BACKUP_ID) INTO Y_BACKUP_ID  FROM M_BACKUP_CATALOG WHERE SYS_START_TIME = (SELECT max(SYS_START_TIME) FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME = 'complete data backup');


BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID :Y_BACKUP_ID COMPLETE;


END;

[former_member611865]

Hi, Gopinath,

Thank you for your answer.

I execute the query you suggensted, but got same error...

Could not execute 'DO BEGIN DECLARE Y_BACKUP_ID BIGINT; SELECT SUM(BACKUP_ID) INTO Y_BACKUP_ID FROM M_BACKUP_CATALOG ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ":Y_BACKUP_ID": line 9 col 44 (at pos 278)

Looked like "SUM(BACKUP_ID)" didn't work as we expected. Is there any other way to get only BACKUP_ID value without header?

Thank you.

[gopinath_kolli]

The first query(SELECT statement) is working good now.

Now the problem is with second query(BACKUP). Please check that. Not sure about that query.

[rinaldos]

Is there a solution for the second query yet?

BEGIN

DECLARE I_BACKUPID BIGINT;

SELECT TOP 1 BACKUP_ID into I_BACKUPID
FROM M_BACKUP_CATALOG
WHERE ENTRY_TYPE_NAME = 'complete data backup'
AND SYS_START_TIME < (SELECT REPLACE(TO_CHAR(ADD_DAYS(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), -7)),'-','')
FROM DUMMY)
ORDER BY SYS_START_TIME DESC;

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID :I_BACKUPID;

ALTER SYSTEM RECLAIM LOG;

END;


Above also ended with:

Could not execute 'create procedure

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "I_BACKUPID"