Question Re: SQL Statement too long in Technology Q&A

SQL Statement too long

raffaelw8 — Mon, 28 Mar 2022 15:42:39 GMT

Hi everyone,

I have googled all day long and also tried some attempts from this forum without any success. I want to run a SQL Query inside my HANA Express edition. My query has a total of 30 Million characters and about 401k subquerries.

The goal is to run through each and every cell of a db and calculate a value out of it.

The query looks like this:

SELECT(...(SELECT A FROM "HANA_XS_BASE"."test_file" LIMIT 1 OFFSET 0)*(-0.041558202)+(SELECT B FROM "HANA_XS_BASE"."test_file" LIMIT 1 OFFSET 0)*(0.05298289)+...

I already know that it works, when I run only the first line of code.

HANA only allows queries with up to 16 million characters. Maybe someone here has a good idea how I could run the whole query. Or maybe even a better way to build the statement ? 🙂

So far I have tried to create a variable for big pieces or just small ones like "HANA_XS_BASE"."test_file" (which is always the same) to replace a huge amount of characters with a variable. Apparently that didn't work, maybe because I am not too familiar with variables in SQL ^^

Re: SQL Statement too long

Vitaliy-R — Mon, 28 Mar 2022 19:41:20 GMT

It would help to better understand what you are trying to achieve with this query.

It seems like you are aggregating a sum of a product (*) of the same one value from `SELECT A FROM "HANA_XS_BASE"."test_file" LIMIT 1 OFFSET 0` with some numbers? Where are these numbers coming from?

Re: SQL Statement too long

raffaelw8 — Tue, 29 Mar 2022 06:22:37 GMT

Hi Witalij,

you are right. My starting point is a calculation of sum and products. It calculates a value out of the cells of a db. My goal is to let HANA calculate this for me via a SQL query. The decimal numbers that I am multiplying are generated by another code. The values inside the cells are also decimal numbers.

For example, if I have a db with the titles A, B, C, ... and rows 1, 2, 3, 4, ...

The function

(...(A1)*(-0.041558202)+(B1)*(0.05298289)+...

would be changed into

SELECT(...(SELECT A FROM "HANA_XS_BASE"."test_file" LIMIT 1 OFFSET 0)*(-0.041558202)+(SELECT B FROM "HANA_XS_BASE"."test_file" LIMIT 1 OFFSET 0)*(0.05298289)+...

It's just about a mathematical function that needs to be calculated by HANA and the function is filled with values out of a db 🙂

Re: SQL Statement too long

Vitaliy-R — Wed, 30 Mar 2022 13:28:53 GMT

Hi Raffael.

As we discussed, it is better if you save data in the table using 3 columns: "X", "Y", "value" -- it will help you then to match values by references.

As well, if you want to see examples of integrating HANA and Python using `hana-ml`: https://github.com/SAP-samples/hana-ml-samples

Good luck!