[SOLVED] Select Value of Parameter in Array with Denodo (VQL)

Select Value of Parameter in Array with Denodo (VQL)

I am trying to do something that seems simple but cannot find the right syntax for Denodo's VQL (Virtual Query Language). I have a string like this: XXXX-YYYY-ZZZZ-AAAA-BBBB in a column called "location" that varies in length, and I want to get the value of the fourth set (i.e. AAAA in this example). I am using the Denodo split function like this:

SELECT SPLIT("-",location)[3] AS my_variable FROM my_table

However, the [3] doesn't work. I've tried a bunch of variations:

SELECT SPLIT("-",location)[3].value AS my_variable FROM my_table

SELECT SPLIT("-",location).column3 AS my_variable FROM my_table

etc.

Can someone please help me figure out the right syntax to return a single parameter from an array? Thank you!

Solution

SELECT field_1[3].string 
FROM (SELECT split('-', 'XXXX-YYYY-ZZZZ-AAAA-BBBB') as field_1)

You have to do it using a subquery because the syntax to access the element of an array (that is, [<number>]) can only be used with field names. You cannot use something like [4] next to the result of a expression.