Msg 319, Level 15, State 1 - SQL Server Forums

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

Msg 319, Level 15, State 1

Author

Topic

lanti_p
Starting Member

15 Posts

Posted - 2011-12-23 : 06:20:55

Hello gurus,

I need some help with these query of mine
The overall structure is


with tblshn as
(
select fileds...
from tbl1
where conditions1
)

select fields...
from tbl2
where conditions2
and id not in (select id from tblshn )

union 

select tblshn.*
from tblshn

now, I want to join all the data set from above results, with another table, so I go and apply


select *, tbl3.*
from (

with tblshn as
(
select fileds...
from tbl1
where conditions1
)

select fields...
from tbl2
where conditions2
and id not in (select id from tblshn )

union 

select tblshn.*
from tblshn 

) as masterdata
inner join tbl3 on .....

I am getting this error:

quote:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ')'.

can anyone help me on this?

thank you all and regards,
lanti_p

lanti_p
Starting Member

15 Posts

Posted - 2011-12-23 : 06:24:32

quote:
Originally posted by GilaMonster

Duplicate post. No replies to this thread please. Direct replies to:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169509[/url]

--
Gail Shaw
SQL Server MVP

Sorry for incovenience ... I already deleted the dublicated topic

thnx

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-23 : 06:25:16

You can't embed a CTE inside a SQL statement. It's the other way around. So, more something like this

WITH    tblshn
          AS ( SELECT   fields
               FROM     tbl1
               WHERE    conditions1 
             )
    SELECT  * ,
            tbl3.*
    FROM    ( SELECT    fields
              FROM      tbl2
              WHERE     conditions2
                        AND id NOT IN ( SELECT  id FROM    tblshn )
            ) AS masterdata
INNER JOIN tbl3 on ....

--
Gail Shaw
SQL Server MVP

lanti_p
Starting Member

15 Posts

Posted - 2011-12-23 : 06:36:28

Thank you for your response, I got the idea now ...
many thanks again

quote:
What about the performance of this query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 06:37:29

whats the need of a cte here? I feel like you can dispense with it and join to tbl1 directly in main query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lanti_p
Starting Member

15 Posts

Posted - 2011-12-23 : 06:54:59

The overall idea is to get all ID from the CTE part and UNION them with other ID found in second part ... but we do not want dublicates
so I go and apply

AND id NOT IN ( SELECT  id FROM    tblshn )

Can I avoid usage of CTE? Because query is taking so much execution time ...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-23 : 06:56:44

You can substitute the name of the CTE in that subquery with the definition, but it'll do little to nothing for execution time. CTEs are inlined during parsing anyway, they're for readability, not performance.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 06:59:14

[code]
select *, tbl3.*
from (

select fields...
from tbl2 t2
where conditions2
and not exists
(
select 1
from tbl1
where conditions1
and t2.id= yourcorrespondingfield
)
union

select tblshn.*
from tblshn

) as masterdata
inner join tbl3 on .....

..
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources