iT邦幫忙

0

MSSQL語法增加一組Range之後就無法執行了

動態語法 mssql sql
johnstudy 2023-04-28 16:21:17 ‧ 1046 瀏覽

  • 分享至 

  • xImage

在SSMS查詢中
在以下語法有增加一組Range，沒增加前可以正常執行
增加了之後就出現了很奇怪的錯誤，請各位前輩幫忙看一下

DECLARE @Ser1 NVARCHAR(10); 
DECLARE @Ser2 NVARCHAR(10); 
DECLARE @Ser3 NVARCHAR(10); 
DECLARE @Ser4 NVARCHAR(10); 
DECLARE @Ser5 NVARCHAR(10); 
DECLARE @Ser6 NVARCHAR(10); 
DECLARE @Ser7 NVARCHAR(10); 
DECLARE @Ser8 NVARCHAR(10); 
DECLARE @Ser9 NVARCHAR(10); 
DECLARE @Ser10 NVARCHAR(10); 
DECLARE @Ser11 NVARCHAR(10); 
DECLARE @Ser12 NVARCHAR(10); 
DECLARE @Ser13 NVARCHAR(10); 
DECLARE @DynamicSQL NVARCHAR(MAX);

SELECT @Ser1 = CAST(YEAR(GETDATE()) - 1911 AS NVARCHAR) + RIGHT(REPLICATE('0', 2) + CAST(MONTH(GETDATE()) AS NVARCHAR), 2) + '%';
SELECT @Ser2 = CAST(YEAR(DATEADD(MONTH, -1, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -1, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser3 = CAST(YEAR(DATEADD(MONTH, -2, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -2, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser4 = CAST(YEAR(DATEADD(MONTH, -3, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -3, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser5 = CAST(YEAR(DATEADD(MONTH, -4, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -4, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser6 = CAST(YEAR(DATEADD(MONTH, -5, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -5, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser7 = CAST(YEAR(DATEADD(MONTH, -6, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -6, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser8 = CAST(YEAR(DATEADD(MONTH, -7, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -7, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser9 = CAST(YEAR(DATEADD(MONTH, -8, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -8, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser10 = CAST(YEAR(DATEADD(MONTH, -9, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -9, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser11 = CAST(YEAR(DATEADD(MONTH, -10, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -10, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser12 = CAST(YEAR(DATEADD(MONTH, -11, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -11, GETDATE())) AS NVARCHAR), 2) + '%' ;
SELECT @Ser13 = CAST(YEAR(DATEADD(MONTH, -12, GETDATE())) - 1911 AS NVARCHAR) +  RIGHT(REPLICATE('0', 2) + CAST(MONTH(DATEADD(MONTH, -12, GETDATE())) AS NVARCHAR), 2) + '%' ;

-- Prepare dynamic SQL statement
SET @DynamicSQL = N'

WITH CTE AS (
    SELECT 
          [MNO]
          ,[PEC]
          ,SUM([MQTY]) AS TOTQTY,
          CASE 
              WHEN mdat LIKE ''' + @Ser1 + '%'' THEN ''' + @Ser1 + '''
              WHEN mdat LIKE ''' + @Ser2 + '%'' THEN ''' + @Ser2 + '''
			  WHEN mdat LIKE ''' + @Ser3 + '%'' THEN ''' + @Ser3 + '''
			  WHEN mdat LIKE ''' + @Ser4 + '%'' THEN ''' + @Ser4 + '''
			  WHEN mdat LIKE ''' + @Ser5 + '%'' THEN ''' + @Ser5 + '''
			  WHEN mdat LIKE ''' + @Ser6 + '%'' THEN ''' + @Ser6 + '''
			  WHEN mdat LIKE ''' + @Ser7 + '%'' THEN ''' + @Ser7 + '''
			  WHEN mdat LIKE ''' + @Ser8 + '%'' THEN ''' + @Ser8 + '''
			  WHEN mdat LIKE ''' + @Ser9 + '%'' THEN ''' + @Ser9 + '''
			  WHEN mdat LIKE ''' + @Ser10 + '%'' THEN ''' + @Ser10 + '''
			  WHEN mdat LIKE ''' + @Ser11 + '%'' THEN ''' + @Ser11 + '''
			  WHEN mdat LIKE ''' + @Ser12 + '%'' THEN ''' + @Ser12 + '''
			  WHEN mdat LIKE ''' + @Ser13 + '%'' THEN ''' + @Ser13 + '''
          END AS mdateRange
      FROM FD1
      WHERE (mdat LIKE ''' + @Ser1 + ''' 
			OR mdat LIKE ''' + @Ser2 + ''' 
			OR mdat LIKE ''' + @Ser3 + ''' 
			OR mdat LIKE ''' + @Ser4 + ''' 
			OR mdat LIKE ''' + @Ser5 + ''' 
			OR mdat LIKE ''' + @Ser6 + '''
			OR mdat LIKE ''' + @Ser7 + '''
			OR mdat LIKE ''' + @Ser8 + '''
			OR mdat LIKE ''' + @Ser9 + '''
			OR mdat LIKE ''' + @Ser10 + '''
			OR mdat LIKE ''' + @Ser11 + '''
			OR mdat LIKE ''' + @Ser12 + '''
			OR mdat LIKE ''' + @Ser13 + ''')

      GROUP BY 
          [MNO]
          ,[PEC]
          ,CASE 
              WHEN mdat LIKE ''' + @Ser1 + '%'' THEN ''' + @Ser1 + '''
              WHEN mdat LIKE ''' + @Ser2 + '%'' THEN ''' + @Ser2 + '''
			  WHEN mdat LIKE ''' + @Ser3 + '%'' THEN ''' + @Ser3 + '''
			  WHEN mdat LIKE ''' + @Ser4 + '%'' THEN ''' + @Ser4 + '''
			  WHEN mdat LIKE ''' + @Ser5 + '%'' THEN ''' + @Ser5 + '''
			  WHEN mdat LIKE ''' + @Ser6 + '%'' THEN ''' + @Ser6 + '''
			  WHEN mdat LIKE ''' + @Ser7 + '%'' THEN ''' + @Ser7 + '''
			  WHEN mdat LIKE ''' + @Ser8 + '%'' THEN ''' + @Ser8 + '''
			  WHEN mdat LIKE ''' + @Ser9 + '%'' THEN ''' + @Ser9 + '''
			  WHEN mdat LIKE ''' + @Ser10 + '%'' THEN ''' + @Ser10 + '''
			  WHEN mdat LIKE ''' + @Ser11 + '%'' THEN ''' + @Ser11 + '''
			  WHEN mdat LIKE ''' + @Ser12 + '%'' THEN ''' + @Ser12 + '''
			  WHEN mdat LIKE ''' + @Ser13 + '%'' THEN ''' + @Ser13 + '''
          END
)
SELECT
    ''1-500'' AS Range,
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser13 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser13,3) + '年' + left(right(@Ser13,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser12 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser12,3) + '年' + left(right(@Ser12,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser11 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser11,3) + '年' + left(right(@Ser11,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser10 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser10,3) + '年' + left(right(@Ser10,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser9 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser9,3) + '年' + left(right(@Ser9,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser8 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser8,3) + '年' + left(right(@Ser8,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser7 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser7,3) + '年' + left(right(@Ser7,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser6 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser6,3) + '年' + left(right(@Ser6,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser5 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser5,3) + '年' + left(right(@Ser5,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser4 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser4,3) + '年' + left(right(@Ser4,3),2) + '月') + ',
    COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser3 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser3,3) + '年' + left(right(@Ser3,3),2) + '月') + ',
    COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser2 + ''' THEN 1 ELSE NULL END) AS  ' + QUOTENAME(left(@Ser2,3) + '年' + left(right(@Ser2,3),2) + '月') + ',
    COUNT(CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange = ''' + @Ser1 + ''' THEN 1 ELSE NULL END) AS  ' + QUOTENAME(left(@Ser1,3) + '年' + left(right(@Ser1,3),2) + '月') + '
FROM CTE

--當我在FROM CTE後加了這段語法之後就卡住無法執行了

UNION ALL

SELECT
    ''501-1000'' AS Range,
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser13 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser13,3) + '年' + left(right(@Ser13,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser12 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser12,3) + '年' + left(right(@Ser12,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser11 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser11,3) + '年' + left(right(@Ser11,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser10 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser10,3) + '年' + left(right(@Ser10,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser9 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser9,3) + '年' + left(right(@Ser9,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser8 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser8,3) + '年' + left(right(@Ser8,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser7 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser7,3) + '年' + left(right(@Ser7,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser6 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser6,3) + '年' + left(right(@Ser6,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser5 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser5,3) + '年' + left(right(@Ser5,3),2) + '月') + ',
	COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser4 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser4,3) + '年' + left(right(@Ser4,3),2) + '月') + ',
    COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser3 + ''' THEN 1 ELSE NULL END) AS ' + QUOTENAME(left(@Ser3,3) + '年' + left(right(@Ser3,3),2) + '月') + ',
    COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser2 + ''' THEN 1 ELSE NULL END) AS  ' + QUOTENAME(left(@Ser2,3) + '年' + left(right(@Ser2,3),2) + '月') + ',
    COUNT(CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange = ''' + @Ser1 + ''' THEN 1 ELSE NULL END) AS  ' + QUOTENAME(left(@Ser1,3) + '年' + left(right(@Ser1,3),2) + '月') + '
FROM CTE
--刪除↑這段語法就又可以執行了

';

-- Execute dynamic SQL
EXEC sp_executesql @DynamicSQL;

這些訊息跟我的語法相關的地方也沒錯，不知道問題是出在哪，請各位先進們指點一下

訊息 102，層級 15，狀態 1，行 75
接近 ')' 之處的語法不正確。
訊息 4145，層級 15，狀態 1，行 83
在有預期條件的內容中指定的非布林類型運算式，接近 'BETWEE'。

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

0
rogeryao
iT邦超人 8 級 ‧ 2023-04-28 21:50:58

...END AS mdateRange
CASE WHEN TOTQTY BETWEEN 1 AND 500 AND mdateRange
CASE WHEN TOTQTY BETWEEN 501 AND 1000 AND MDateRange

mdateRange = MDateRange ???
系統是否有設定區分大小寫 ?

測試一下,只執行 SELECT ''501-1000'' AS Range,.... 看看是否正確.

johnstudy iT邦新手 5 級 ‧ 2023-05-08 15:25:29 檢舉

大小寫應該沒差，只執行 SELECT ''501-1000'' AS Range,...
這樣也是可以的，兩個上下顛倒也不行

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙