Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: MERGE statement with "with" clause

    Hi I am having problem while using with clause in following syntax:

    MERGE INTO tablename tr
    USING ( with WTab as (
    select distinct xxx as ID1, yyy as ID2 from table2
    )
    select ID1 as key from WTab
    Union all
    select ID2 as key from WTab
    ) KeyList
    ON tr.Key=KeyList.Key
    When NOT MATCHED THEN
    INSERT ......

    We get the syntax error saying "as following with Wtab is not valid"
    where as when I remove the "with clause" and use the same table outside then it works fine.

    So Is there any other systax I should be following or whether this MERGE statement can not work with "With" Clause?

    Any help is appreciated.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2Aishani
    Hi I am having problem while using with clause in following syntax:

    MERGE INTO tablename tr
    USING ( with WTab as (
    select distinct xxx as ID1, yyy as ID2 from table2
    )
    select ID1 as key from WTab
    Union all
    select ID2 as key from WTab
    ) KeyList
    ON tr.Key=KeyList.Key
    When NOT MATCHED THEN
    INSERT ......

    We get the syntax error saying "as following with Wtab is not valid"
    where as when I remove the "with clause" and use the same table outside then it works fine.

    So Is there any other systax I should be following or whether this MERGE statement can not work with "With" Clause?

    Any help is appreciated.

    I do not know if it will fix the entire problem, but the Common Table Expression (CTE) should look like this:

    with WTab (ID1, ID2) as (
    select distinct xxx, yyy from table2
    )
    select ID1 as key from WTab
    Union all
    select ID2 as key from WTab


    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The MERGE statement expects a "fullselect". And a CTE is not a fullselect but rather part of a SELECT statement only. Thus, the above won't work and you should simply use traditional subselects.

    http://publib.boulder.ibm.com/infoce...c/r0000877.htm
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2007
    Posts
    3
    Quote Originally Posted by ARWinner
    I do not know if it will fix the entire problem, but the Common Table Expression (CTE) should look like this:

    with WTab (ID1, ID2) as (
    select distinct xxx, yyy from table2
    )
    select ID1 as key from WTab
    Union all
    select ID2 as key from WTab


    Andy
    Thanks Andy for your reply,
    I tried doing that too but could not work out.

    Thanks,

  5. #5
    Join Date
    Aug 2007
    Posts
    3
    Quote Originally Posted by stolze
    The MERGE statement expects a "fullselect". And a CTE is not a fullselect but rather part of a SELECT statement only. Thus, the above won't work and you should simply use traditional subselects.

    http://publib.boulder.ibm.com/infoce...c/r0000877.htm

    Thanks Mr. Knut Stolze,
    Thanks for clearing my doubt, I understand that why it was not working it.

    Do you thing If I create view and use the view instead or the CTE,
    I know that this will again same as executing the distinct select twice.

    I just thought to check your suggestions!

    Thanks for your reply.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you worry about running a DISTINCT twice, you should first check the plan that this is really happening.

    Besides, your query above is definitively not seminatically equivalent to:
    Code:
    SELECT DISTINCT id1 FROM ...
    UNION ALL
    SELECT DISTINCT id2 FROM ...
    Your query removes duplicate pairs of id1, id2.

    Maybe we (you) should first figure out what exactly you want to do and then write the query. Optimizing it should be done afterwards.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2007
    Posts
    1

    From a colleague of the poster

    Thanks you all for your help.

    We certainly agree with your comments about "running the DISTINCT twice"

    We simplified the code for the posting.

    Table2 is extraordinarily large. The id1 and id2 are not indexed (this query is run once per update of table_2) and have combined very few combinations.

    By doing it this way the query runs in minutes insead of an hour.

    and infact we have id1,id2,id3,id4.

    So if you have a better way of getting distinct lists of four unindexed columns please let us know.

Posting Permissions

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