cte SQL0104N

gjv · #1 (**permalink**) 10-17-11, 05:14

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

dr_te_z · #2 (**permalink**) 10-17-11, 05:54

Quote:

Originally Posted by gjv

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 ...."

make it look like this:

Code:

WITH my_scope AS
   (SELECT result.col0 FROM session.t1 result)
DELETE FROM session.t1 
WHERE session.t1.col0 IN (select * from my_scope);

So, it is 1 statement but the CTE definition(2) come first.

I can only judge the CTE syntax. If all this works on temp-tables I do not know.

gjv · #3 (**permalink**) 10-20-11, 02:14

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)

Appreciate suggestions

gjv · #4 (**permalink**) 10-20-11, 02:22

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

tonkuma · #5 (**permalink**) 10-20-11, 05:10

Quote:

... INSERT works fine with my previous query, ...

Analogy is a good starting point to study new things.
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.

Internet Services

Web Development

Digital Marketing

Consumer Tech