Can we use Common-Table expressions with datasource SQL-templating?

[Stefanie]

Hi,

I was just wondering if it is possible to use common-table expressions (CTE) with the sql-templating feature for datasources. I have a datasource, that currently uses the <customSQL> sql tag, however I am trying to convert this to use the sql-templating tags (selectClause, whereClause, etc), as these use pagination.

However, I'm not sure if it's possible to include a CTEs while using the sql-templating function.

Given that CTEs are actually placed before the select statement, it doesn't seem possible to use the WITH statement, unless creating a custom query, which doesn't paginate results.

Is there anything like a <withClause>, that would allow a custom CTE to be written? that would just be inserted before the select statement.

[Blama]

Hi Stefanie,

why doesn't customSQL support pagination?
At least JDBC pagination should be working, which is better anyway, as it reduces SQL hard parses.
Which DB are you using?

Best regards
Blama

[Stefanie]

Hi Blama
Thanks for the quick reply!

I was under the impresion that the customSql tag didn't use pagination, as this is what is says on the CustomQuerying documentation:

Full custom queries specified via <customSQL> provide complete flexibility, but they cannot be used for automatic data paging; if you use a full custom query, all data returned by the query will be delivered to the client, which may be inefficient. To retain automatic data paging, implement your customizations by replacing just specific clauses of the query, via <whereClause>, <selectClause>, and the other clause-by-clause replacement features.

The database I'm using in Sql Server 2012
https://www.smartclient.com/smartgwt...mQuerying.html

[Blama]

Hi Stefanie,

I'm not sure about this. IMHO there is no tecnical reason that it shouldn't work with JDBC windowed Selects.
I understand that this might be more complicated if you are using ROWNUM or SQL LIMIT, but all this is for Isomorphic to answer.

I agree that a <WithClause> makes sense, but I do think it's not there, yet.
You could put your with-clause in a DB-view, though. In general a CTE is nothing more than a non-stored view.

Best regards
Blama

[Stefanie]

Blama

I'll give that a go - I'll move the CTE into a DB view. Then I should just be able to select what I need from that view.

Thanks for your help Blama, it's very much appreciated!

Regards,