SQL datasource and aggregated data

[rolf.woll]

Hi,

I´m struggling with a SQL datasource with a aggregated value similar to the Dynamic Reporting showcase.
(http://www.smartclient.com/smartgwte...amic_reporting)

In this showcase, sorting based on aggregated values(quantity, totalCost) works fine. However, the qualifyColumnNames are set to false.

Unfortunately, I have a complex query where I need qualified column names. The aggregated names in my query then will be qualified with the table name causing the query to fail. I.e. in the Dynamic Reporting showcase,the generated SQL will use "dynRepOrderItem.totalCost" instead of "totalCost" in the order by clause, which I believe will cause the query to fail?

Any way around this?

Next question is regarding criteria on aggregated fields. In the Dynamic Reporting showcase, is it possible to create criteria on the totalCost field that will be correctly generated in a HAVING SQL clause?

Finally, what is the purpose of the groupWhereClause?

Regards
Rolf


SmartClient Version: v9.0d_2013-07-04/PowerEdition Deployment (built 2013-07-04)

[Isomorphic]

We can't comment in your need for qualified vs unqualified names in different parts of the SQL without seeing the entire query and tables. However if you plan to use a HAVING clause, that currently requires <customSQL> rather than clause by clause overrides.

[rolf.woll]

Hi,

thanks for the reply. Okay, so I need to use the customSql.

However, it would be nice to use $defaultOrderClause and $defaultWhereClause within my customSQL.

Is it possible to have qualified names for all fields except for fields that are marked as customSQL="true" in the field definition but still included in the operationbinding through customFields?

I´m not sure why you need my tables and my sql statement to answer this question. Should this be working and could the problem be due to my setup?

My table setup is as follows:

table A, fields id, deleted
table B, fields id, a_id(foreign key to table A), deleted

My datasource is something like this:

<DataSource tableName="A" serverType="sql" dbName="MSSQL">
<fields>
<field name="id" type="sequence" primaryKey="true" hidden="true"/>
<field name="deleted" title="Deleted"/>
<field name="no_of_bs" title="No of Bs" type="integer" customSQL="true"/>
</fields>
<operationBindings>
<operationBinding operationType="fetch" sqlPaging="jdbcScroll" customFields="no_of_bingos" excludeCriteriaFields="no_of_bingos">
<customSQL>
<![CDATA[
select a.id, a.deleted, count(*) as no_of_bs
FROM A left outer join B on B.a_id = A.id and B.deleted="false"
WHERE $defaultWhereClause
GROUP BY A.id, A.deleted
#if($defaultOrderClause != "") ORDER BY $defaultOrderClause #end
]]>
</customSQL>
</operationBinding>
</operationBindings>
</DataSource>

I need qualified names since A and B both have columns named "deleted". I would like to reuse the defaultWhereClause and defaultOrderClause as much as possible.

defaultWhereClause works fine as I am able to exclude any no_of_bs criteria from the where clause using the exludeCriteriaFields column.

The problem is that when I order the grid according to the no_of_bs column, the query fails as the generated SQL query is ORDER BY A.no_of_bs whilst it should have been ORDER BY no_of_bs.

I also need to add a HAVING clause at the end of the query for the no_of_bs criteria. Is there any way to retrieve the sql for the criteria(as it would have been used in the where clause) or do I need to create it myself?

Regards
Rolf

[Isomorphic]

Whether or not we automatically qualify columns has to do with whether a join is involved, whether you're doing the join in custom SQL or we're generating the SQL, whether aggregation is applied, whether there's column aliasing going on, and because of all of the above, what clause we're talking about.

So that's why it can't be discussed without details of the query and tables.

From a look at your query below, why wouldn't in work to enable qualifyColumnNames="true"? Or even just to set tableName="a" on the "deleted" field?

[rolf.woll]

When column names are qualified, the aggregated field is qualified also, i.e. "no_of_bs" in my previous example.

This causes the $defaultOrderClause to fail since it tries to do a
ORDER BY A.no_of_bs instead of ORDER BY no_of_bs.

So, is there any way to prevent the qualification of aggregated fields/custom fields in the $default velocity templates?

Rolf

[rolf.woll]

Nevermind,

I´m able to work around my problem by disabling qualified columns, and explicitly adding a table name for the "deleted" field. This makes me able to use the $defaultOrderClause in my customSQL directly.

Another question. The groupWhere clause in the Dynamic Reporting example, what is the purpose of this field? Any documentation for this field?

Thanks
Rolf

[Isomorphic]

Sorry for the delay on this. "groupWhereClause" is a currently undocumented, unsupported property that causes criteria to be applied *after* the entire normal query has been run, and it's implemented via a subselect.

This is necessary to make filtering on aggregated values (total quantity and total sales) work as expected in this sample. However this sample really shouldn't be using an undocumented property however, so we'll be rearranging the sample to get rid of it, and probably introducing a documented, supported variation on this as part of 4.1.

[rolf.woll]

Okay. That was clarifying.

Thank you.

Rolf