CODEWITHSUNDEEP
sqlsql-server
user7617078
user7617078

Reputation: 65

SQL Error on WITH clause

I'm trying to code something like below but getting error:

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.

I tried to insert WITH but it doesn't help.

select @flag = 1 from tabC where 1 = (WITH mul AS
(
   SELECT REPLICATE(CHAR(32 + N), 4) AS val
   FROM (select top 95 row_number() over(order by t1.number) as N
         from   master..spt_values t1)  AS s
)
SELECT *
FROM tabC c
WHERE LEN(CompanyName) > 4
  AND EXISTS (SELECT 1
              FROM mul
              WHERE CHARINDEX(mul.val,c.CompanyName) > 0))

Upvotes: 0

Views: 64

Answers (1)

gbn
gbn

Reputation: 432662

You can't embed a CTE

Try this

DECLARE @flag bit; --semi colon is important

WITH mul AS
(
   SELECT REPLICATE(CHAR(32 + N), 4) AS val
   FROM (select top 95 row_number() over(order by t1.number) as N
         from   master..spt_values t1)  AS s
)
SELECT @flag = 1
FROM tabC c
WHERE val = 1
  AND LEN(CompanyName) > 4
  AND EXISTS (SELECT 1
              FROM mul
              WHERE CHARINDEX(mul.val,c.CompanyName) > 0)

Upvotes: 2

Related Questions