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 edited the query and i got the below error; any idea

SELECT 'PV' || LPAD('0', 6 - LENGTH(MAX(IFNULL(ink, 0)))) || CAST(MAX(IFNULL(ink, 0)) + 1 AS varchar(6))

FROM (SELECT MAX(coalesce(substring("CounterRef", 3, LENGTH(IFNULL("CounterRef", 0))), 0)) AS ink

    FROM OVPM T0

    UNION ALL

    SELECT MAX(coalesce(substring("CounterRef", 3, LENGTH(IFNULL("CounterRef", 0))), 0)) AS  ink

    FROM OPDF T0

    WHERE t0."ObjType" = '46') AS tk;

regards

Justice