We have a Denodo service we want to create but have ran into some issues while creating it. The customer required a few things for this service. A default set of parameters if they don't send any themselves, which we are using the 'ExecuteIfIsNotNull' in conjunction with @WHEREEXPRESSION to solve. e.g. they want field1 in ('1',2','3') and field2 in ('a','b').
They also require that that outbount format to use nesting. Note, the fields that they want to search on are inside the nest. It seems in order to allow searching on these fields we also had to expose those throughout the view outside the array that is generated. This causes a problem though because these parameters become part of the "group by" and start to divide an array into multiple records instead of all contained in 1 array.
Example, they are searching for the following, size in('large', 'medium') and state in('NY'). Instead of nesting in 1 group, it will create 2 arrays, 1 for large/NY and 1 for medium/NY. If I remove the fields so they not also at the parent level, thus they are removed from the group by but they are not searchable now, it correctly nests them all into 1 array.
Basically is there a way to expose certain fields to be searchable in a nest that will allow multiple values for the same field be searched on at the same by, but not create multiple arrays.