Convert SQL Query

[millicentdark]

Dear expert,

i have been trying to convert this SQL query to HANA but i am having difficulties and it is not working,

Please below is the SQL QUERY and i will be glad if i can have an urgent assistance with this query to HANA;

NumRange integer;

NumRange := 6;

SELECT 'PV' || replicate('0', :NumRange - LENGTH(MAX(IFNULL(Incnum, 0)))) || CAST(MAX(IFNULL(Incnum, 0)) + 1 AS varchar(6)) FROM (SELECT MAX(coalesce(substring(CounterRef, 3, LENGTH(IFNULL(CounterRef, 0))), 0)) AS "incnum" FROM OVPM T0 UNION ALL SELECT MAX(coalesce(substring(CounterRef, 3, LENGTH(IFNULL(CounterRef, 0))), 0)) AS "incnum" FROM OPDF T0 WHERE t0.ObjType = '46') AS Tb1;

I am using the query above to auto increase the value in 6 figures with the prefix PV.

Urgent help will be appreciated.

Regards

Justice

[millicentdark]

Hi Lars,

i did as you asked but i get the below error;

DO BEGIN

NumRange integer;

NumRange := 6;

SELECT 'PV' || replicate('0', :NumRange - LENGTH(MAX(IFNULL(Incnum, 0)))) || CAST(MAX(IFNULL(Incnum, 0)) + 1 AS varchar(6))

FROM (SELECT MAX(coalesce(substring("CounterRef", 3, LENGTH(IFNULL("CounterRef", 0))), 0)) AS "incnum" FROM OVPM T0

UNION ALL SELECT MAX(coalesce(substring("CounterRef", 3, LENGTH(IFNULL("CounterRef", 0))), 0)) AS "incnum"

FROM OPDF T0 WHERE T0."ObjType" = '46') AS Tb1

END;

[lbreddemann]

DECLARE NumRange Integer;

Just putting the variable name and type doesn't cut it with SQLScript.