关键字'with'附近的语法不正确 [英] Incorrect syntax near the keyword 'with'

问题描述

我的SQL查询是：

my sql query is :

INSERT INTO #TempUsersAudt 
SELECT  p.IPersonID, p.sFirstName, p.sLastName, p.sLoginName, p.sEmail, REPLACE(o.sOrgPath, '>', '> ') AS sOrgPath,p.INodeID,  p.sPersonality, fAccountType = CASE p.fAccountType WHEN 'S' THEN 'Full Access' ELSE 'Regular'   END, fRights = CASE p.fRights WHEN 'A' THEN 'Administer' WHEN 'W' THEN 'Write'WHEN 'R' THEN 'Read' END 
From auditor.People p with (nolock) 
INNER JOIN auditor.OrgTree o with (nolock) ON o.INodeID = p.INodeID 
INNER JOIN @tbl_TYPE_LOOKUP TL ON TL.Type = p.fAccountType
WHERE p.fAccountStatus <> 'D'   AND p.fAccountType IN('R', 'R') AND o.sOrgPath LIKE 'Acme Corporation>%'    AND p.sLoginName LIKE '%j%' AND p.sEmail LIKE '%j%' AND TL.TypeName  LIKE '%j%'

我收到的错误是：



关键字'with'附近的语法不正确。如果此语句是公用表表达式，xmlnamespaces子句或更改跟踪上下文子句，则必须以分号结束前一个语句。



请帮我查找在哪里我做错了

i am getting error is :

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.

please help me to find where i am doing wrong

推荐答案

好的 - 我希望它会抛出另一个问题:-)



尝试在此交易的顶部放置 SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED 并将与（nolock）子句一起保留off - 这是一种（非常）类似的效果。



另外看看这个链接是否真的需要搭配（nolock）

http://www.sqlservercentral.com/Forums/Topic696705-360-1.aspx [ ^ ]

Ok - I was hoping that it would throw out another problem :-)

Try putting SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED at the top of this transaction and leaving the with(nolock) clauses off - it's a (very) similar effect.

Also have a look at this link re whether you really do need the with(nolock)
http://www.sqlservercentral.com/Forums/Topic696705-360-1.aspx[^]

Frist提示：

尝试替换这部分查询：

Frist hint:
Try to replace this part of query:

SELECT  p.IPersonID, p.sFirstName, p.sLastName, p.sLoginName, p.sEmail, REPLACE(o.sOrgPath, '>', '> ') AS sOrgPath,p.INodeID,  p.sPersonality, fAccountType = CASE p.fAccountType WHEN 'S' THEN 'Full Access' ELSE 'Regular'   END, fRights = CASE p.fRights WHEN 'A' THEN 'Administer' WHEN 'W' THEN 'Write'WHEN 'R' THEN 'Read' END 

with：

with:

SELECT  p.IPersonID, p.sFirstName, p.sLastName, p.sLoginName, p.sEmail, REPLACE(o.sOrgPath, '>', '> ') AS sOrgPath, p.INodeID,  p.sPersonality,
    CASE WHEN p.fAccountType='S' THEN 'Full Access'
         ELSE 'Regular' END AS fAccountType,
    CASE WHEN p.fRights='A' THEN 'Administer'
         WHEN p.fRights='W' THEN 'Write'
         WHEN p.fRights='R' THEN 'Read'
    END AS fRights

第二个提示：

删除 WITH [ ^ ] （nolock）声明附近。

Second hint:
Remove WITH[^] near (nolock) statement.

引用：

以下表提示允许带有和不带WITH关键字： NOLOCK，READUNCOMMITTED，UPDLOCK，REPEATABLEREAD，SERIALIZABLE，READCOMMITTED，FASTFIRSTROW，TABLOCK，TABLOCKX，P AGLOCK，ROWLOCK，NOWAIT，READPAST，XLOCK和NOEXPAND 。如果在没有WITH关键字的情况下指定这些表提示，则应单独指定提示。例如：

The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone. For example:

FROM t (TABLOCK)

我希望它有所帮助;）

I hope it helps ;)

这篇关于关键字'with'附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！