You can translate the question and the replies:

How to get a length of the array in Denodo?

I have a column in the Denodo view with the type ARRAY. How can I get the length of this array? Standard sql `ARRAY_LENGTH` function is not working.
user
18-07-2022 02:12:32 -0400

2 Answers

Hello, I am able to retrieve the length of an array from an array type column in a Virtual DataPort view by doing the following: Syntax: **COUNT((<ARRAY_FIELD>).value)** For example: a view dv_test with columns test_customer_code and test_details(with sub elements) grouped by test_customer_code View: dv_test test_customer_code | test_details(agent_id,date) 01 | 55 | 2022-01-01 | 01 | 66 | 2022-09-09 | 02 | 12 | 2022-01-05 | 02 | 43 | 2022-09-07 | 02 | 76 | 2022-03-02 | To retrieve the length of the array test_details.agent_id based on test_customer_code: select test_customer_code, **count((test_details).agent_id)** as count from dv_test where test_customer_code=value group by customer_code Output: test_customer_code | count 01 | 2 02 | 3 For further guidelines on compound types like array, please refer to [Management of Compound Values](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/advanced_characteristics/management_of_compound_values/management_of_compound_values#:~:text=of%20Compound%20Values-,Management%20of%20Compound%20Values,-Virtual%20DataPort%20has) and the [Processing of Compound Types: Example](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/advanced_characteristics/management_of_compound_values/processing_of_compound_types_example#:~:text=Compound%20Types%3A%20Example-,Processing%20of%20Compound%20Types%3A%20Example,-Imagine%20that%20you) documentation. Hope this helps!
Denodo Team
18-07-2022 16:51:44 -0400
Ok Thanks Your Explaination. I Think we can Use this one COUNT((<ARRAY_FIELD>).value) Notice that you can not use COUNT() over the [aggregate](https://hkrtrainings.com/denodo-tool) function LIST() directly.
user
20-07-2022 01:13:56 -0400
You must sign in to add an answer. If you do not have an account, you can register here