问题描述
关键字UNPIVOT附近有错误信息错误语法
Have this Error Message Incorrect Syntax near the keyword UNPIVOT
------------------------
---------UNPIVOT------------------
----------------------------------
SET @sql = @sql + ' UNPIVOT
(
OPINION_CNT FOR OPINIONS IN
(ACCEPT_TAT,BEYOND_TAT,EXACT_TAT)
) '
--------------------------------------------------------------------------------------------------------
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT A.BRN_NAME ,A.PROD_SERV , A.COMP_TYPE,
P.PROD_SERV_TAT,
COUNT(*) AS OVER_CNT ,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) < COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS ACCEPT_TAT,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) > COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS BEYOND_TAT,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) = COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS EXACT_TAT,
(SELECT COUNT(*) FROM RESPONSE BB WHERE A.BRN_NAME=BB.BRN_NAME) AS GRP_CNT ,OPINIONS,OPINION_CNT
FROM RESPONSE A
LEFT JOIN BRANCH C ON A.BRN_NAME = C.BRN_NAME
LEFT JOIN PROD_SERV P ON A.PROD_SERV = P.PROD_SERV_NAME
LEFT JOIN PAYMAST B ON A.LNAME = B.LNAME
AND A.FNAME = B.FNAME
AND A.EMPNO = B.EMPNO
WHERE (A.COMP_DATE BETWEEN @CFROM AND @CTO)'
IF @CINCIDENT <>'ALL'
BEGIN
IF @CINCIDENT = 'OPENED'
SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT >= GETDATE()'
ELSE IF @CINCIDENT = 'CLOSED'
SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT < GETDATE()
AND A.RESOLUTION_STAT_FRONT NOT IN ("BAD","UNHAPPY","UNSATISFACTORY")'
ELSE IF @CINCIDENT = 'UNRESOLVED'
SET @sql = @sql + ' AND A.COMP_DATE+P.PROD_SERV_TAT < GETDATE()
AND A.RESOLUTION_STAT_FRONT IN ("BAD","UNHAPPY","UNSATISFACTORY")'
END
------------------------------------
---------BRANCH---------------------
------------------------------------
IF @CDDL1 <>'ALL'
SET @sql = @sql + ' AND C.BRN_NAME=@CDDL1'
----------------------------------
---------UNPIVOT------------------
----------------------------------
SET @sql = @sql + ' UNPIVOT
(
OPINION_CNT FOR OPINIONS IN
(ACCEPT_TAT,BEYOND_TAT,EXACT_TAT)
) '
--------------------------
--------------------------
---GROUPING & ORDERING---
--------------------------
--------------------------
SET @sql = @sql + ' GROUP BY A.BRN_NAME ,A.PROD_SERV , A.COMP_TYPE,
P.PROD_SERV_TAT '
------------------------------------- -------------------------------------------------- -------------------------------------------------- ---
--------------------------------------- -------------------------------------------------- -------------------------------------------------- -
消息156，15级，状态1，行17
关键字'UNPIVOT'附近的语法不正确。
（1对（s）受影响）
我做错了什么？
请协助
谢谢
我的尝试：
正在进行的开发审查了许多解决方案都可以使用
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'UNPIVOT'.
(1 row(s) affected)
What am I doing wrong ?
Please assist
Thanks
What I have tried:
On going development reviewed a number of solutions all to o avail
推荐答案
SELECT A.BRN_NAME ,A.PROD_SERV , A.COMP_TYPE,
P.PROD_SERV_TAT,
COUNT(*) AS OVER_CNT ,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) < COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS ACCEPT_TAT,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) > COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS BEYOND_TAT,
SUM(CASE WHEN DATEADD(DD,P.PROD_SERV_TAT, A.COMP_DATE) = COALESCE(A.DAT_COMP_INV_CLOSE0,A.DAT_COMP_INV_CLOSE,A.DATE_COMP_CLOS_FRMT)THEN 1 ELSE 0 END) AS EXACT_TAT,
(SELECT COUNT(*) FROM RESPONSE BB WHERE A.BRN_NAME=BB.BRN_NAME) AS GRP_CNT ,OPINIONS,OPINION_CNT
FROM RESPONSE A
LEFT JOIN BRANCH C ON A.BRN_NAME = C.BRN_NAME
LEFT JOIN PROD_SERV P ON A.PROD_SERV = P.PROD_SERV_NAME
LEFT JOIN PAYMAST B ON A.LNAME = B.LNAME
AND A.FNAME = B.FNAME
AND A.EMPNO = B.EMPNO
WHERE (A.COMP_DATE BETWEEN @CFROM AND @CTO) UNPIVOT
(
OPINION_CNT FOR OPINIONS IN
(ACCEPT_TAT,BEYOND_TAT,EXACT_TAT)
) GROUP BY A.BRN_NAME ,A.PROD_SERV , A.COMP_TYPE,
P.PROD_SERV_TAT
看看带下划线的线条。 UNPIVOT 指令就在 WHERE 子句之后。正确的SQL语句应如下所示：
Take a look at underlined line. UNPIVOT instruction is right after a WHERE clause. A proper SQL statement should looks like:
SELECT <unpivoted_columns>
FROM
(
--pivoted data here
) AS pvt
UNPIVOT
(
--
) AS unpvt
所以，你必须相应地更改你的sql代码。
注意：我要更改<$ c的声明$ c> @sql 变量为： DECLARE @sql NVARCHAR（MAX）= N'';
有关详细信息，请参阅： []
这篇关于在关键字UNPIVOT附近出现此错误消息的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！