You can translate the question and the replies:

Managing Null Array or no result due to where clause in flattening a view

I have a view where certain array that I need to flatten may be null or may not contain the specific row that I am looking for. When flattening such array, I get an empty result. How do I manage such array during flattening? Eg. Say I have this data ID | Name | Description 1 | [Array] | [Array] 2 | [Array] | <null> Within the Name [Array], there are 2 fields NameType & Name. Same thing with Description [Array] - 2 fields DescType & Description. So when I flattened Name, I get something like ID | NameType | Name | Description 1 | T1 | Name1 of T1 | [Array] 2 | T1 | Name2 of T1 | <null> 2 | T2 | Name2 of T2 | <null> 2 | T3 | Name2 of T3 | <null> The problem with flattening that I am facing is that sometimes, the Type that I am looking for is not there. Thus, when my where condition is NameType = 'T2', I ended up with the first record missing. Results will be ID | NameType | Name | Description 2 | T2 | Name2 of T2 | <null> Instead, I still want the first record, let it be null, such as ID | NameType | Name | Description 1 | <null> | <null> | [Array] 2 | T2 | Name2 of T2 | <null> How do I solve this problem? The same thing will happen with the description too because there wasn't any description item in the array for ID=2. So I ended up with no records at all. Instead, what I need is a result that looks like this. ID | NameType | Name | DescType | Description 1 | <null> | <null> | T2 | Description1 of T2 2 | T2 | Name2 of T2 | <null> | <null> But all I get is nothing. So how do I manage this during flattening? Use some sort of Join? Many thanks.
user
15-07-2015 04:14:57 -0400

7 Answers

Hi! I'm not sure if I'm understanding exactly what you have described. If from this table: ID | NameType | Name | Description 1 | T1 | Name1 of T1 | [Array] 2 | T1 | Name2 of T1 | <null> 2 | T2 | Name2 of T2 | <null> 2 | T3 | Name2 of T3 | <null> You want to get this results: ID | NameType | Name | DescType | Description 1 | <null> | <null> | T2 | Description1 of T2 2 | T2 | Name2 of T2 | <null> | <null> what you have to do is, to flatten the description and then filter using: NameType = 'T2' OR DescType='T2' Anyway, in this case, the result you will get would be: ID | NameType | Name | DescType | Description 1 | T1 | Name1 of T1 | T2 | Description1 of T2 2 | T2 | Name2 of T2 | <null> | <null> No nulls for NameType and Name. When flattening, the values remains the same for all the rows. Hope this helps!
Denodo Team
15-07-2015 06:31:16 -0400
Close, but not quite, I only want to see T2. So following your example, I am able to get as what you have shown, but that's not the end result that I want. the end result I want is ID | NameType | Name | DescType | Description 1 | <null> | <null> | T2 | Description1 of T2 2 | T2 | Name2 of T2 | <null> | <null> as I only want to see everything of T2. and even if there isn't any T2 text of name nor description, I still want to see that record. Eg. result, if say ID 3 hasn't any name nor description ID | NameType | Name | DescType | Description 1 | <null> | <null> | T2 | Description1 of T2 2 | T2 | Name2 of T2 | <null> | <null> 3 | <null> | <null> | <null> | <null> How is this possible? I've produced a very simple json file for you to load & test. [{"ID":1,"Name":[{"NameType":"T1","Name":"Name1 of T1"}],"Description":[{"DescType":"T1","Description":"Description1 of T1"},{"DescType":"T2","Description":"Description1 of T2"}]}, {"ID":2,"Name":[{"NameType":"T1","Name":"Name2 of T1"},{"NameType":"T2","Name":"Name2 of T2"},{"NameType":"T3","Name":"Name2 of T3"}]}, {"ID":3}] These are my vqls for flattening CREATE VIEW f_flattentest FOLDER = '/3. derived views/others' AS SELECT id, name, description FROM FLATTEN (flattentest AS v, v.jsonarray); CREATE VIEW f_f_flattentest FOLDER = '/3. derived views/others' AS SELECT id, description, nametype, name FROM FLATTEN (f_flattentest AS v, v.name); CREATE VIEW f_f_f_flattentest FOLDER = '/3. derived views/others' AS SELECT id, nametype, name, desctype, description FROM FLATTEN (f_f_flattentest AS v, v.description); and finally, the selection that you recommended CREATE VIEW p_f_f_f_flattentest FOLDER = '/3. derived views/others' AS SELECT f_f_f_flattentest.id AS id, f_f_f_flattentest.nametype AS nametype, f_f_f_flattentest.name AS name, f_f_f_flattentest.desctype AS desctype, f_f_f_flattentest.description AS description FROM f_f_f_flattentest WHERE (nametype = 'T2' OR desctype = 'T2'); But this IS NOT the end result that I can use. The result that I can use has to look like the first flattened view except the [Array]... of name & description fields are expanded but it's essentially ALL records are to be shown & show the name and description too only if T2 is available. Just like described earlier. ID | NameType | Name | DescType | Description 1 | <null> | <null> | T2 | Description1 of T2 2 | T2 | Name2 of T2 | <null> | <null> 3 | <null> | <null> | <null> | <null> How is this possible? Many thanks.
user
 Edited on: 28-07-2017 05:35:22 -0400
I finally found an answer to this & it is to use outer joins. First, select the T2 NameType (removing description array) CREATE VIEW f_flattentest_n FOLDER = '/3. derived views/others' AS SELECT id, nametype, name FROM FLATTEN (f_flattentest AS v, v.name) WHERE v.nametype = 'T2'; Then do the same for DescType (removing name array) CREATE VIEW f_flattentest_d FOLDER = '/3. derived views/others' AS SELECT id, desctype, description FROM FLATTEN (f_flattentest AS v, v.description) WHERE v.desctype = 'T2'; Finally, join them together with the original (removing the original's name & description array) CREATE VIEW f_flattentest_nd FOLDER = '/3. derived views/others' AS SELECT f_flattentest.id AS id, f_flattentest_n.nametype AS nametype, f_flattentest_n.name AS name, f_flattentest_d.desctype AS desctype, f_flattentest_d.description AS description FROM (f_flattentest AS f_flattentest LEFT OUTER JOIN f_flattentest_n AS f_flattentest_n ON f_flattentest.id = f_flattentest_n.id ) LEFT OUTER JOIN f_flattentest_d AS f_flattentest_d ON f_flattentest.id = f_flattentest_d.id ; Any other ideas? Cheers.
user
 Edited on: 28-07-2017 05:35:22 -0400
Hello, I didn't understand completely right what you wanted to get. I thought that you those nulls were some kind of mistake or misunderstanding, it sounded like a bit strange scenario. Sorry about that. Yes, you are right. The solution you have found is the right way to do this. If you are interested in a more flexible solution, and you want to get the same results for the case of 'T1'. Remember that you can use parameters as entries to views. This way, instead of creating the f_flattentest_n and f_flattentest_d views filtering by 'T2', you can use a parameter.
Denodo Team
16-07-2015 04:58:22 -0400
I'm not quite sure what "use parameters as entries to views" means. All I can think about is trying to create and join up sub-queries. Can you do up a VQL example based the json data please? Cheers.
user
 Edited on: 28-07-2017 05:35:22 -0400
Hi! What I meant was that, for instance, in the case of the view 'f_flattentest_d' you can use an expression like this: CREATE VIEW f_flattentest_d AS SELECT id AS id, name AS name, description AS description, desctype AS desctype, desctypeparam FROM FLATTEN (f_flattentest AS v, v.description) WHERE v.desctype = desctypeparam USING PARAMETERS ( desctypeparam : text 'T2'); This way, your static expression WHERE v.desctype = 'T2' has been changed for a flexible solution. The same can be applied to 'f_flattentest_n'. I recommend you to read the Virtual DataPort Administration Guide's section: 'Parameters of Derived Views' section to get further details about Parameters and how can be easily configured thought graphically. Hope this can be helpful!
Denodo Team
17-07-2015 03:37:02 -0400
ok, I think that has reached the limitation of express edition there.. Syntax error: Creation of views with parameters is not allowed by the license But thanks anyway. I'll use what works for the POC. cheers.
user
 Edited on: 28-07-2017 05:35:22 -0400
You must sign in to add an answer. If you do not have an account, you can register here