[SA 7.1.166 Pro] - SQLite WITH statement

[SA 7.1.166 Pro] - SQLite WITH statement

SQL Editor properly reports that 3 rows were affected for the query below, however it fails to yield a result set (using SQLite3 ODBC Driver).

	Code:
	WITH re_num AS ( SELECT vai.id_activity,vai.row_num AS old_row_num ,( SELECT count (1) FROM v_activity_inventory AS vai2 WHERE 1 = 1 AND vai2.row_num <= vai.row_num ) AS new_row_num FROM v_activity_inventory AS vai ) SELECT * FROM re_num;

Thank you very much. I have logged this issue. It looks like SQL Assistant's database interface handling interaction with SQLite doesn't detect the result set is coming back.

This appears to be an issue in ODBC driver, not in SQL Assistant. Here is a copy of the response we got from ODBC driver author.

	Quote:
	Thanks for reporting this issue. The problem is that the current driver does not detect the new CTE syntax as a SELECT statement and therefore does not produce a result set since it thinks it is something like an INSERT/UPDATE/DELETE statement. Will look into it but this will take some time. BR, Christian

I suspected as much, thank you for confirmation. Are they going to get back to you when they fixed the issue?

The ODBC driver developer notified us of the latest update fixing issues with CTE processing. It's a pre-lease version made available to us for testing and it seems to be working well. Please contact the author if you would like to try that update.