SQLScript: scalar type is not allowed

Question

I have the following sql command:

SELECT DFT(weighted, 32768 ORDER BY rel_id).REAL as four FROM :fith

This function returns an array with 32769 elements in one cell.

Now I want to use the unnestfunction to split the result. Therefore I need to save the results in a variable.

The following command throws an error:

my_array = SELECT DFT(weighted, 32768 ORDER BY rel_id).REAL as four FROM :fith;
===>    invalid datatype: Column FOUR of table variable MY_ARRAY has an unsupported data type (ARRAY)

Well okay... after the declaration of my my_array to an array, I'm getting this error:

    declare my_array double array;
    my_array = SELECT DFT(weighted, 32768 ORDER BY rel_id).REAL as four FROM :fith;
==>scalar type is not allowed: MY_ARRAY:

Why do I want to do this over such a detour? The documentation says:

The UNNEST function cannot be referenced directly in a FROM clause of a SELECT statement

Whats wrong? What can I do now?

score 0 · Answer 1 · answered Nov 23 '17 at 07:59

The problem is most likely that you are trying to put an SQL-result in an array.
You could however store the SQL-result in a local table variable.

In your case I would create a cursor and loop over the result set.

Within the loop-body you can then split the cell-content using a custom split-string function as described in the thread HANA: Split string?

SQLScript: scalar type is not allowed

1 Answers1