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

[millicentdark]

[millicentdark]

Hi all,

i have changed the query without the declaration ; Below is my new query and the error i am getting;

SELECT 'PV' || LPAD('0', 6 - 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;

[michael_eaton3]

You need to align your column names, you are using "Incnum" and "incnum" - these are not similar.

Michael

PS Where has the exasperated emoticon gone?

[millicentdark]

Hi Lars,

I am lost now. Can you please simplify it for me with your query. I am still getting errors,

Below;

DO BEGIN

DECLARE NumRange integer;

NumRange := 6;

SELECT 'PV' || LPAD('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;

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.

[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.

[millicentdark]

Hi experts,

below is my converted query to HANA and the error i am getting.

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;

[lbreddemann]

Alright

1. In order to use SQLScript for the variables in SAP HANA Studio, you have to enclose the whole code block into DO BEGIN .... <your code here> END;

2. Column names in SAP HANA are only treated case sensitive when you put them in double quotes ("CounterRef" works, but CounterRef will be interpreted as COUNTERREF)

Make sure to quote all column names.

[lbreddemann]

If you want 'urgent' assistance how about you go an pay someone to fix your code or to train you in using SQL?

How do you think anyone can efficiently fix your problem when you don't even bother sharing the error message you get?

[millicentdark]

Lars,

I am not sure you understand my request. This is an SQL query and all I need is someone who probably has converted a similar query to HANA syntax.

It is not about an error message.