CTE issues - 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

SQL Server 2008 Forums

Transact-SQL (2008)

CTE issues

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-27 : 22:55:37

hi

I am passing variables into CTE and it compliant about:
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 8
Incorrect syntax near ','. How should do it? Thannks

Declare @index1 as nvarchar(50), @index2 as nvarchar(50)
set @index1 = '5'
Set @index2 = '90'

WITH Sales_CTE AS
(
Select testdata from dbo.testdata where testdata like '%' + @index1 + '%'
), Sales_CTE2 as
(
Select * from Sales_CTE where testdata like '%' + @index2 + '%'
)

Select * from Sales_CTE2

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-27 : 23:13:06

hi

i have solved it. Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-09-27 : 23:59:17

hi

My initial statement returns too many row:
Declare @index1 as nvarchar(50), @index2 as nvarchar(50)
set @index1 = '90'
Set @index2 = '4';

WITH Sales_CTE AS
(
Select testdata from dbo.testdata where testdata like '%' + @index1 + '%'
), Sales_CTE2 as
(
Select * from Sales_CTE where testdata like '%' + @index2 + '%'
)

Select * from Sales_CTE2

i then change to this to find exact match but it seems that freetext is not support in CTE. I have tought of using regex but do not know how to? Are there any better approach? Thanks

Declare @index1 as nvarchar(50), @index2 as nvarchar(50)
set @index1 = '90'
set @index2 = '4'
;
WITH Sales_CTE AS
(
Select testdata from dbo.testdata where FREETEXT(testdata, @index1)
),Sales_CTE2 AS
(
Select testdata from dbo.Sales_CTE where FREETEXT(testdata, @index2)
)

Select * from Sales_CTE2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources