var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: cte SQL0104N
i've a query which looks like..
"DELETE FROM session.t1 WHERE session.t1.col0 IN ( WITH my_scope AS ( SELECT result.col0 FROM session.t1 result), literals AS ...."
This fails with SQL0104N code..
[IBM][CLI Driver][DB2/NT64] SQL0104N An unexpected token "AS" was found following "0 IN ( WITH my_scope". Expected tokens may include: "JOIN". SQLSTATE=42601
not sure what is wrong being a new to Db2..any help is appriciated
make it look like this:
Originally Posted by gjv
So, it is 1 statement but the CTE definition(2) come first.
WITH my_scope AS
(SELECT result.col0 FROM session.t1 result)
DELETE FROM session.t1
WHERE session.t1.col0 IN (select * from my_scope);
I can only judge the CTE syntax. If all this works on temp-tables I do not know.
thanks for looking into dr_te_z
i tried your suggestion but that failed with
"ODBC error. SQLSTATE: 42601 Native error: -104
Message: [IBM][CLI Driver][DB2/NT64] SQL0104N An unexpected token "DELETE FROM " was found following t1 result)
one thing i would like to add is INSERT works fine with my previous query, now that is impacted with new approach..is there any diff with cte syntax for insert and delete
Analogy is a good starting point to study new things.
... INSERT works fine with my previous query, ...
But, it would not give you an accurate conclusion.
You can't use common-table-expression in a DELETE statement.
See Syntax of DELETE statement in Information Center
DELETE - IBM DB2 9.7 for Linux, UNIX, and Windows
Anyhow, what result do you want to get by using common-table-expression?
Unless you want to use recursive-cte, you may get the intermittent result table without using cte.
Tags for this Thread