Author |
Message |
gemisigo
Joined: 11 Mar 2010 Posts: 2170
|
|
[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).
 |
 |
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;
|
|
|
Mon Sep 15, 2014 10:35 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7966
|
|
|
|
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.
|
|
Mon Sep 15, 2014 11:52 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7966
|
|
|
|
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.
 |
 |
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 |
|
|
Tue Sep 16, 2014 7:33 am |
|
 |
gemisigo
Joined: 11 Mar 2010 Posts: 2170
|
|
|
|
I suspected as much, thank you for confirmation. Are they going to get back to you when they fixed the issue?
|
|
Tue Sep 16, 2014 8:09 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7966
|
|
|
|
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.
|
|
Wed Sep 17, 2014 7:44 am |
|
 |
|