How can I handle sql functions which returning arrays?

[Former Member]

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

[pfefferf · Accepted solution]

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

[Former Member · Accepted solution]

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

[pfefferf · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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