Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 cte expression

Author  Topic 

tariq2
Posting Yak Master

125 Posts

Posted - 2011-07-14 : 08:36:03
Hi, what is wrong with the following cte.
I am trying to create t1 and t2 with an intention of creating an inner join between the two..

However I receive the following error:
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.

Many thanks in advance


with t1
AS
(
select a.gldoc,a.GLJELN from openquery(symprod_etldev,'select * from proddta.f0911 where glco = ''10002'' and globj = ''445791'' ')a

left outer join

(select GLDOC,GLJELN from F0911 where GLCO = '10002' and GLOBJ = '445791')b

on a.gldoc = b.gldoc and a.gljeln = b.gljeln

where b.GLJELN IS NULL
)
,T2
AS
(
select * from openquery(symprod_etldev,'select * from proddta.f0911 where glco = ''10002'' and globj = ''445791'' ')
)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 08:39:49
You also need the final select that joins the two tables - for example like this:

WITH t1
AS
(
SELECT
a.gldoc,
a.GLJELN
FROM
OPENQUERY(
symprod_etldev,
'select * from proddta.f0911 where glco = ''10002'' and globj = ''445791'' '
)a
LEFT OUTER JOIN (
SELECT
GLDOC,
GLJELN
FROM
F0911
WHERE
GLCO = '10002'
AND GLOBJ = '445791'
)b
ON a.gldoc = b.gldoc AND a.gljeln = b.gljeln
WHERE
b.GLJELN IS NULL
)
,T2
AS
(
SELECT
*
FROM
OPENQUERY(
symprod_etldev,
'select * from proddta.f0911 where glco = ''10002'' and globj = ''445791'' '
)
)
SELECT * FROM t1 INNER JOIN t2 ON t1.gldoc = t2.gldoc
Go to Top of Page
   

- Advertisement -