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

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 Andy for your reply,
I tried doing that too but could not work out.

Thanks,

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.

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

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.