Symptoms

Use WITH clause in smart object query

I am getting an error when I attempt to use the WITH clause with recursive query.

I attached a copy simple query and the error message
"ERROR: Invalid SQL syntax, The SQL construct (e.g. SELECT, INSERT, DELETE or UPDATE) must be specified.

Here is some recursive query documentation: https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
 

Diagnoses

ADO query does not supported ‘Nested selected’ statement.
 

Resolution

The WITH clause is not supported, in addition the ADO query does not supported ‘Nested selected’ statement, see link below.
http://help.k2.com/onlinehelp/k2blackpearl/DevRef/4.6.7/webframe.html_Paging_with_the_ADO_Provider_and_Direct_Execution.html

A simple idea is to wrap the recursive query in a stored procedure or a SQL view and surface it through smartobject layer.