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]