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]

DO BEGIN

DECLARE 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;

Does SAP HANA have a function for REPLICATE ?

[lbreddemann]

Nope, that's MS SQL Server specific.

From the example it looks like you want to yield padding...

If that's correct, then LPAD Function (String) - SAP HANA SQL and System Views Reference - SAP Library is what you might use.

Something along the lines of

SELECT

   'PV' || LPAD ("Incnum", :NumRange,  '0')

FROM ...

should do the trick.

LPAD does the length calculation for you, so your statement gets simpler as well.

Also, since you already do the NULL handling in the subquery, you don't need to repeat it in the projection list.