Select Value of Parameter in Array with Denodo (VQL)

Question

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!

score 3 · Accepted Answer · answered Sep 12 '19 at 05:06

3

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.

answered Sep 12 '19 at 05:06

Montecarlo

1,239
1
11
24

@greenbellpepper I would be very grateful if you upvote my answer :) – Montecarlo Oct 01 '19 at 00:03
What if the arrays are different lengths and you want to select the last item, which can be [4], [5], or [6]. Is there a way to always select the last piece? – luanstat Mar 12 '20 at 16:45
@luanstat This is the best I came up with ```WITH v AS (SELECT split('-', 'XXXX-YYYY-ZZZZ-AAAA-BBBB') as field_1) SELECT last(string) FROM FLATTEN test_flatten AS V (V.field_1);``` – Montecarlo Feb 14 '22 at 19:23

Colorado Techie · Answer 2 · 2019-09-10T20:56:53.483

This question helps: https://community.denodo.com/answers/question/details?questionId=90670000000CcQPAA0

I got it working by creating a view that saves the array as a field:

CREATE OR REPLACE VIEW p_sample_data FOLDER = '/stack_overflow' 
AS SELECT bv_sample_data.location AS location
, bv_sample_data.id AS id
, split('-', location) AS location_array 
FROM bv_sample_data;

Notice I created a column called location_array?

Now you can use a select statement on top of your view to extract the information you want:

SELECT location, id, location_array[2].string
FROM p_sample_data

location_array[2] is the 3rd element, and the .string tells denodo you want the string value (I think that's what it does... you'd have to read more about Compound Values in the documentation: https://community.denodo.com/docs/html/browse/6.0/vdp/vql/advanced_characteristics/management_of_compound_values/management_of_compound_values )

Another way you could probably do it is by creating a view with the array, and then flattening the array, although I haven't tried that option.

Update: I tried creating a view that flattens the array, and then using an analytics (or "window") function to get a row_number() OVER (PARTITION BY id order by ID ASC), but analytic/window functions don't work against flat file sources.

So if you go the "flatten" route and your source system doesn't work with analytic fuctions, you could just go with a straight rownum() function, but you'd have to offset the value by column number you want, and then use remainder division to pull out the data you want.

Like this:

--My view with the array is called p_sample_data
CREATE OR REPLACE VIEW f_p_sample_data FOLDER = '/stack_overflow' AS 
SELECT location AS location
   , id AS id
   , string AS string
   , rownum(2) AS rownumber 
FROM FLATTEN p_sample_data AS v ( v.location_array);

Now, with the rownum() function (and an offset of 2), I can use remainder division in my where clause to get the data I want:

SELECT location, id, string, rownumber
FROM f_p_sample_data
WHERE rownumber % 5 = 0

Frankly, I think the easier way is to just leave your location data in the array and extract out the nth column with the location_array[2].string syntax, where 2 is the nth column, zero based.

Select Value of Parameter in Array with Denodo (VQL)

2 Answers2