/scripts/ahub.form.attachments.js
0

How can I handle sql functions which returning arrays?

Dec 06, 2016 at 02:14 PM

68

avatar image

Hi,

I want to use the function "DFT" in my stored procedure.
DFT is returning an array. How can I handle with the result?

The following query produce an error:

test = select dft(valll, 32768 order by dates).phase from :var5;
==> invalid datatype: Column DFT(valll,32768ORDERBYDATES) of table variable TEST has an unsupported data type (ARRAY)

After the declaration I'm getting this error:

declare test double array;test = select dft(valll, 32768 order by dates).phase from :var5;==>scalar type is not allowed: TEST:

I want to use the results of the DFT function.

I tried this one but this just returns 1024 items...

select 
PHASE 
from UNNEST(
(SELECT DFT(valll, 32768 ORDER BY dates).PHASE FROM :var5)
) AS UNNESTED_DFT("PHASE");

Thanks!

Tobias

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Dec 07, 2016 at 02:01 PM
0

Hello Tobias,

your last statement is the correct one. As you asked in another question UNNEST has no limitation.

In which evironment do you execute the statement? In the SQL console or somewhere else?

Regards,
Florian

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Sorry for the two threads...

We are using the HCP. The Database has the version 1.00.122.04.1478575636.

I'm execute the SQL in Hana Studio and in my xsjs project too. With the same result.

Tobias

Edit: This SQL works... but as you can see, it is not very nice and I have to use a loop to go through all 32K items...

select 
* 
from 
unnest((
select subarray(wert, 1, 1024) 
from((
select 
dft(value, 32768 order by id).amplitude as wert 
from "XXYYZZ"."TEST"
))
)) as unnested(amplitude);
0

Using the same revision, the above (first) select works for me to get all the 32k entries in one step.

0

OK seems to be a mistake from our platform. On my trial account everythings works fine.. we will open a ticket. Thanks anyway!

0

Hi Florian,

could you tell me how i can save the array fom the dft function (see my errors)?

After the HCP Trial Database updatet to the newest version I'm getting the same error as before.. :/

0
Skip to Content