Msg 102,Level 15,State 1,Line 15''''附近的语法错误
问题描述:
我一直在为这个查询争取2天,但仍然无法找到问题所在。Msg 102,Level 15,State 1,Line 15''''附近的语法错误
的SQL查询是这样的:
select t2.transno,
case
when (Select Count (*)
From v1
where v1.transno < v1.transno) > 1
then (
case
when (Select Top 1 v1.transno
From v1
Where v1.transno < v1.transno
Order By v1.transno Desc)=(T2.transno-1)
then ' '
else 'Missing Invoice')
else ''
end AS Marker
FROM v1 AS T2
它给我的错误信息:
Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ')'.
答
你的第二个case语句缺少结束,它需要。试试这个:
select t2.transno,
case
when (Select Count (*)
From v1
where v1.transno < v1.transno) > 1
then (
case
when (Select Top 1 v1.transno
From v1
Where v1.transno < v1.transno
Order By v1.transno Desc)=(T2.transno-1)
then ' '
else 'Missing Invoice') end
else ''
end AS Marker
FROM v1 AS T2
答
我猜你忘了把结束在此声明:else 'Missing Invoice')
尝试像else 'Missing Invoice') end
select t2.transno,
case
when (Select Count (*)
From v1
where v1.transno < v1.transno) > 1
then (
case
when (Select Top 1 v1.transno
From v1
Where v1.transno < v1.transno
Order By v1.transno Desc)=(T2.transno-1)
then ' '
else 'Missing Invoice'
end)
else ''
end AS Marker
FROM v1 AS T2
+0
我已经尝试过您的建议,但得到此error.Msg 4104,级别16,状态1,行34多部分标识符“ t2.transno“不能被绑定。 – Monshine 2011-01-28 06:57:18
答
v1.transno < v1.transno
始终为false =零行。
所以您的查询,可以更为严重的是改写为
SELECT v1.transno, 'Missing Invoice' AS Marker FROM v1
,它可以更简单地表达
select t2.transno,
case
when foo.maxtransno IS NULL THEN 'Missing Invoice'
ELSE ' '
END AS Marker
FROM
v1 AS T2
OUTER APPLY
(Select MAX(v1.transno) AS maxtransno
From v1
Where v1.transno < t2.transno
HAVING MAX(v1.transno) = T2.transno-1
) foo
我想你最好能够给我们的表,数据和所需的输出。 SELECT中每行有两个相关的子查询,这意味着性能不佳。如果它很快,那是因为优化器意识到v1.transno
gbn
2011-01-28 06:13:25
它用于检查发票编号的顺序,如果缺少一个编号,那么它会在标记列中显示缺少的发票。我已经完成了在访问它工作正常,但是当我更改语法在SQL Server 2005中运行它,然后得到错误 – Monshine 2011-01-28 06:50:33