[步骤]
prometheus告警
开始时间: 14:15:24
错误代码: N14152419
sql类型: DECLARE
sql: [14:15:24] -------------------------------------------------------------
CODE:N14152419
Error:15_156_1_-2146232060
SQL:declare @cctv nvarchar(200);
set
@cctv = '';
set
@cctv = SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@ShopeeV2OnlineStatus,',') ;declare @newStr nvarchar(500);
set
@newStr = ',' + @cctv + ',';
set
@newStr = replace(@newStr, '1', 'UPLOADING');
set
@newStr = replace(@newStr, '2', 'UPLOADFAILED');
set
@newStr = replace(@newStr, '3', 'UNLIST');
set
@newStr = replace(@newStr, '4', 'NORMAL');
set
@newStr = replace(@newStr, '5', 'BANNED');
set
@newStr = replace(@newStr, '6', 'DELETED');
set
@newStr = substring(@newStr, 2, len(@newStr) -2);
SELECT
alias.AddTime,
alias.OnlineAdminName AS '上架人',
alias.[OrderSourceName] AS '来源渠道',
alias.ShopeeId AS 'ItemId',
alias.ProductSku AS '系统SKU',
pro.ProductGroupSKU AS '母体ID',
alias.Sku AS '渠道SKU',
alias.ParentSku AS '渠道父SKU',
pro.ProductName AS '产品标题',
alias.OriginalPrice AS '当前价格',
alias.Price AS '折扣价格',
alias.Stock AS '可售数',
(
SELECT
ISNULL(SUM(wp.GoodNum), 0) AS GoodNum
FROM
RB_WareHouse AS wh WITH (NOLOCK)
INNER JOIN RB_WareHouse_Product AS wp WITH (NOLOCK) ON wh.CustomerID = wp.CustomerID
AND wh.ID = wp.WareHouseID
WHERE
wp.SKU = alias.ProductSku
AND wh.WareHouseType = 1
) AS '本地仓实际库存',
SixM.SixMonthSaleNum AS '180天销量',
ThreeM.ThreeMonthSaleNum AS '90天销量',
TowM.TowMonthSaleNum AS '60天销量',
M.MSaleNum AS '30天销量',
CASE
alias.IsMultiVariant
WHEN 0 THEN '单属性'
WHEN 1 THEN '多属性'
ELSE ''
END AS '是否多属性'
FROM
(
SELECT
P.CustomerId,
P.OrderSourceId,
P.OrderSourceName,
P.Sku,
P.ParentSku,
P.ShopeeId,
P.ProductSku,
P.[Name],
P.OriginalPrice,
P.Price,
P.Stock,
P.OnlineAdminName,
P.OnlineAdminId,
P.[Status],
P.IsMultiVariant,
P.AddTime
FROM
RB_Shopee_Listing_Parent(NOLOCK) AS P
WHERE
P.CustomerId = @CustomerId
and P.IsGlobalProduct = 0
AND P.IsMultiVariant = 0
AND P.ProductSku <> ''
AND P.ProductSku IS NOT NULL
and ListingVersion = 2
AND P.OrderSourceId IN (SELECT item FROM dbo.CharSplitForIn(@OrderSourceId,','))
AND P.AddTime >= @StartTime
AND P.AddTime <= @EndTime
UNION
SELECT
temp.CustomerId,
temp.OrderSourceId,
temp.OrderSourceName,
temp.Sku,
temp.ParentSku,
temp.ShopeeId,
temp.ProductSku,
temp.[Name],
temp.OriginalPrice,
temp.Price,
temp.Stock,
P.OnlineAdminName,
P.OnlineAdminId,
P.[Status],
P.IsMultiVariant,
temp.AddTime
FROM
dbo.RB_Shopee_Listing (NOLOCK) temp
INNER JOIN RB_Shopee_Listing_Parent(NOLOCK) P ON temp.CustomerId = P.CustomerId
AND temp.OrderSourceId = P.OrderSourceId
AND temp.ParentSku = P.Sku
WHERE
P.CustomerId = @CustomerId
AND P.IsGlobalProduct = 0
AND P.IsMultiVariant = 1
and temp.ProductSku <> ''
and temp.ProductSku is not null
and P.ListingVersion = 2
AND P.OrderSourceId IN (SELECT item FROM dbo.CharSplitForIn(@OrderSourceId,','))
AND P.AddTime >= @StartTime
AND P.AddTime <= @EndTime
) AS alias
LEFT JOIN dbo.RB_Product(NOLOCK) AS pro ON pro.SKU = alias.ProductSku
AND alias.CustomerId = pro.CustomerID
LEFT JOIN (
SELECT
a.SKU,
ISNULL(SUM(a.SingleNum - a.PutNum), 0) AS buyingNum
FROM
RB_Procurement_Detail AS a (NOLOCK)
INNER JOIN RB_Procurement_List AS b (NOLOCK) ON a.P_Code = b.P_Code
AND b.CustomerID = a.CustomerID
AND b.FinishStatus = 0
LEFT JOIN RB_Product_Supplier AS c (NOLOCK) ON c.ID = a.ChangeSupplierID
AND c.CustomerID = a.CustomerID
LEFT JOIN RB_Product_Supplier AS d (NOLOCK) ON d.ID = b.SupplierID
AND d.CustomerID = b.CustomerID
LEFT JOIN RB_WareHouse w (NOLOCK) ON b.CustomerID = w.CustomerID
AND b.WareHouseID = w.ID
WHERE
a.CustomerID = @CustomerId
GROUP BY
a.SKU
) AS buy ON buy.SKU = alias.ProductSku
left join (
SELECT
rs.OrderSourceID,
rs.OrderSourceSKU,
Sum(
CASE
WHEN Datediff(DAY, SalesDay, Getdate()) < 30 THEN SalesNum
ELSE 0
END
) AS MSaleNum
FROM
RB_Report_OrderSourceSKUDay rs WITH(nolock)
inner join RB_OrderSource OS with(nolock) on rs.OrderSourceID = OS.ID
LEFT JOIN RB_WareHouse rw WITH(nolock) ON rw.ID = rs.WareHouseID
WHERE
Datediff(DAY, SalesDay, Getdate()) < 30
AND rs.CustomerID = @CustomerId
AND OS.OrderSourceType = 97
GROUP BY
rs.CustomerID,
rs.OrderSourceID,
rs.OrderSourceSKU
) as M on alias.OrderSourceId = M.OrderSourceID
and alias.Sku = M.OrderSourceSKU
left join (
SELECT
towMS.OrderSourceID,
towMS.OrderSourceSKU,
Sum(towMS.SalesNum) AS TowMonthSaleNum
FROM
dbo.RB_Report_OrderSourceSKUMonth towMS WITH(nolock)
inner join RB_OrderSource OS with(nolock) on towMS.OrderSourceID = OS.ID
LEFT JOIN RB_WareHouse rw WITH(nolock) ON rw.ID = towMS.WareHouseID
WHERE
towMS.CustomerID = @CustomerId
AND (
(
towMS.SalesYear = Year(Dateadd(Month, -1, Getdate()))
AND towMS.SalesMonth = Month(Dateadd(Month, -1, Getdate()))
)
OR(
towMS.SalesYear = Year(Getdate())
AND towMS.SalesMonth = Month(Getdate())
)
)
AND OS.OrderSourceType = 97
GROUP BY
towMS.CustomerID,
towMS.OrderSourceID,
towMS.OrderSourceSKU
) as TowM on TowM.OrderSourceID = alias.OrderSourceId
and TowM.OrderSourceSKU = alias.Sku
left join (
SELECT
ms.OrderSourceID,
ms.OrderSourceSKU,
Sum(ms.SalesNum) AS ThreeMonthSaleNum
FROM
dbo.RB_Report_OrderSourceSKUMonth ms WITH(nolock)
inner join RB_OrderSource OS with(nolock) on ms.OrderSourceID = OS.ID
LEFT JOIN RB_WareHouse rw WITH(nolock) ON rw.ID = ms.WareHouseID
WHERE
ms.CustomerID = @CustomerId
AND (
(
ms.SalesYear = Year(Dateadd(Month, -2, Getdate()))
AND ms.SalesMonth = Month(Dateadd(Month, -2, Getdate()))
)
OR(
ms.SalesYear = Year(Dateadd(Month, -1, Getdate()))
AND ms.SalesMonth = Month(Dateadd(Month, -1, Getdate()))
)
OR(
ms.SalesYear = Year(Getdate())
AND ms.SalesMonth = Month(Getdate())
)
)
AND OS.OrderSourceType = 97
GROUP BY
ms.CustomerID,
ms.OrderSourceID,
ms.OrderSourceSKU
) as ThreeM on ThreeM.OrderSourceID = alias.OrderSourceId
and ThreeM.OrderSourceSKU = alias.Sku
left join (
SELECT
msixM.OrderSourceID,
msixM.OrderSourceSKU,
Sum(msixM.SalesNum) AS SixMonthSaleNum
FROM
dbo.RB_Report_OrderSourceSKUMonth msixM WITH(nolock)
inner join RB_OrderSource OS with(nolock) on msixM.OrderSourceID = OS.ID
LEFT JOIN RB_WareHouse rw WITH(nolock) ON rw.ID = msixM.WareHouseID
WHERE
msixM.CustomerID = @CustomerId
AND (
(
msixM.SalesYear = Year(Dateadd(Month, -5, Getdate()))
AND msixM.SalesMonth = Month(Dateadd(Month, -5, Getdate()))
)
OR (
msixM.SalesYear = Year(Dateadd(Month, -4, Getdate()))
AND msixM.SalesMonth = Month(Dateadd(Month, -4, Getdate()))
)
OR (
msixM.SalesYear = Year(Dateadd(Month, -3, Getdate()))
AND msixM.SalesMonth = Month(Dateadd(Month, -3, Getdate()))
)
OR (
msixM.SalesYear = Year(Dateadd(Month, -2, Getdate()))
AND msixM.SalesMonth = Month(Dateadd(Month, -2, Getdate()))
)
OR(
msixM.SalesYear = Year(Dateadd(Month, -1, Getdate()))
AND msixM.SalesMonth = Month(Dateadd(Month, -1, Getdate()))
)
OR(
msixM.SalesYear = Year(Getdate())
AND msixM.SalesMonth = Month(Getdate())
)
)
AND msixM.CustomerID = 1
and OS.OrderSourceType = 97
GROUP BY
msixM.CustomerID,
msixM.OrderSourceID,
msixM.OrderSourceSKU
) as SixM on SixM.OrderSourceID = alias.OrderSourceId
and SixM.OrderSourceSKU = alias.Sku
WHERE
alias.CustomerId = @CustomerId
AND alias.OrderSourceId IN (SELECT item FROM dbo.CharSplitForIn(@OrderSourceId,','))
AND alias.AddTime >= @StartTime
AND alias.AddTime <= @EndTime
AND pro.ProductState in (SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@ProductState,','))
AND alias.[Status] in(
select
item
from
CharSplitForIn(@newStr, ',')
)
AND alias.OnlineAdminId in (SELECT CONVERT(INT,item) FROM dbo.CharSplitForIn(@OnlineAdminId,','))
PARAMS:@OrderSourceId = 14226 ,@StartTime = 2022-06-16 00:00:00 ,@EndTime = 2022-09-16 23:59:59 ,@ProductState = 0,1 ,@OnlineAdminId = 3120 ,@ShopeeV2OnlineStatus = 4 ,@CustomerId = 1 ,
MESSAGE:Incorrect syntax near the keyword 'SELECT'.
TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 在 System.Data.SqlClient.SqlDataReader.get_MetaData() 在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) 在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) 在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
在 RB.DAL.DBOperator.ExecuteReader(String SQLString, SqlParameter[] cmdParms, Boolean IsWriteDB, Int32 Timeout) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)
在 RB.DAL.DBOperator.ExecuteReader(String SQLString, SqlParameter[] cmdParms, Boolean IsWriteDB, Int32 Timeout)
在 Biz.Irobotbox.ReportCentre.CustomReportService.GetPageDataForCustomReportToSqlDataReader(String firstFileFullPath, RB_CustomReportTask customReportTask, String[] noPermsissionfields, Dictionary2 paramList, List1 exportFields, String orderBy, Int32 timeOut)
在 Biz.Irobotbox.ReportCentre.CustomReportService.ExportCSVBySteam(RB_CustomReportTask customReportTask, String[] noPermsissionfields, Dictionary2 paramValues, List1 exportFields, String orderField, Int32 timeOut)
在 Biz.Irobotbox.ReportCentre.CustomReportService.<>c__DisplayClass34_0.b__2()
在 System.Threading.Tasks.Task.Execute()
在 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
在 System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)
在 System.Threading.ThreadPoolWorkQueue.Dispatch()
[结果]
[期望]
修复 语法错误bug
