Question Re: Convert SQL Query in Technology Q&A

Convert SQL Query

millicentdark — Thu, 22 Sep 2016 17:49:17 GMT

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

Re: Convert SQL Query

lbreddemann — Fri, 23 Sep 2016 00:06:12 GMT

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?

Re: Convert SQL Query

millicentdark — Fri, 23 Sep 2016 04:51:42 GMT

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.

Re: Convert SQL Query

millicentdark — Fri, 23 Sep 2016 07:49:09 GMT

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;

Re: Convert SQL Query

lbreddemann — Fri, 23 Sep 2016 08:38:09 GMT

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.

Re: Convert SQL Query

millicentdark — Fri, 23 Sep 2016 08:48:15 GMT

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;

Re: Convert SQL Query

lbreddemann — Fri, 23 Sep 2016 08:56:13 GMT

DECLARE NumRange Integer;

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

Re: Convert SQL Query

millicentdark — Fri, 23 Sep 2016 09:05:25 GMT

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 ?

Re: Convert SQL Query

lbreddemann — Fri, 23 Sep 2016 09:36:29 GMT

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.

Re: Convert SQL Query

millicentdark — Fri, 23 Sep 2016 09:56:31 GMT

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

Re: Convert SQL Query

millicentdark — Fri, 23 Sep 2016 13:05:39 GMT

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;

Re: Convert SQL Query

michael_eaton3 — Fri, 23 Sep 2016 13:16:27 GMT

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?

Re: Convert SQL Query

millicentdark — Fri, 23 Sep 2016 14:21:36 GMT

Re: Convert SQL Query

millicentdark — Mon, 26 Sep 2016 12:44:58 GMT

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