Denodo 6.0, latest update as of 4/10/2017: Are we able to specify how NULLs are treated in aggregate and windowing functions? I wasn't able to find any information about specifying the handling of NULLs in aggregrate and windowing functions. In particular, I want to specify IGNORE NULLS for the FIRST_VALUE function. It appears this keyword is not accepted as legal VQL. I am unable to return the correct values without it (and without writing some horribly resource hogging -- and slow -- SQL).
10-04-2017 14:26:05 -0400

2 Answers

Hi , In order to handle NULLS I would suggest try one of the following options: If you are using the interpolation variable when creating a base view from query option, then you can use** “ExecuteIfIsNotNull”** function. For example if you want the customerid to be not null then use this function which will execute only if the value is not null. ``` ^ExecuteIfIsNotNull("\"customerId\" : \"",@CID,"\",","\"customerId\" : null,") ``` To know more about "ExecuteIfIsNotNull", read the section EXECUTION CONTEXT OF A QUERY AND INTERPOLATION STRINGS in Advanced VQL Guide. Other operation to manage null is CASE where you can check if the arg is not null. ``` CASE WHEN arg1 IS NOT NULL THEN arg1 ``` The COALESCE could be useful for you too which is similar to the case operator. ``` COALESCE(<param>, <param> [, <paramN>]*) ``` To know more about this function please read the section COALESCE in the Advanced VQL Guide. There are also Comparison Operators like - is Not NULL - is NULL To know more about about these operations read the section COMPARISON OPERATORS in the Advanced VQL Guide. Hope this helps.
Denodo Team
17-04-2017 07:52:03 -0400
Thanks for the tips... after thinking about it a bit more, for first_value() to work correctly the only option would be to construct the "order by" so that NULLs are ordered last. Fortunately that seems to be the default for VDP. Unfortunately, it seems VDP doesn't correctly implement "NULLS FIRST" / "NULLS LAST", as demonstrated by this VQL: Virtual DataPort 6.0 20170220 com.denodo.vdp.jdbc.Driver 6.0 20170220 SELECT 1 AS a FROM dual() UNION ALL SELECT 2 AS a FROM dual() UNION ALL SELECT NULL AS a FROM dual() UNION ALL SELECT 5 AS a FROM dual() ORDER BY a ASC nulls first; a ------ 1 2 5 (null)
09-05-2017 14:31:26 -0400
You must sign in to add an answer. If you do not have an account, you can register here