Results 1 to 5 of 5

Thread: cte SQL0104N

  1. #1
    Join Date
    Oct 2011
    Posts
    3

    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

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by gjv View Post
    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.

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    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

  4. #4
    Join Date
    Oct 2011
    Posts
    3
    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... 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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •