[步骤]
http://10.158.158.22:8080/Manager/index.aspx
自定义报告页面选择【亚马逊FAB库龄库存报告（采集版）-点击【预览】会报错
[结果]
CODE:N1415461
Error:15_156_1_-2146232060
SQL:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY [账号] desc) AS [PageIndex] ,* FROM ( IF OBJECT_ID('Tempdb..#tb_1') IS NOT NULL
BEGIN
DROP TABLE #tb_1
END
CREATE TABLE #tb_1
(
sku VARCHAR(40) PRIMARY KEY
)
INSERT #tb_1
SELECT
DISTINCT
sku
FROM RB_Report_FULFILLMENT_INVENTORY_MANAGE (NOLOCK)
WHERE CustomerID = @CustomerID
AND [snapshot-date] >= DATEADD(MONTH, -1, @Enddate)
AND [snapshot-date] <= @Enddate;
WITH t1
AS ( SELECT a.CustomerID ,
b.DevIDSellerId ,
a.OrderSourceID ,
a.sku,
MAX([snapshot-date]) AS [snapshot-date]
FROM RB_Report_FULFILLMENT_INVENTORY_MANAGE a ( NOLOCK )
LEFT JOIN dbo.RB_OrderSource_EbayAmazonAPI b ( NOLOCK ) ON b.CustomerID = a.CustomerID
AND b.OrderSourceID = a.OrderSourceID
WHERE a.CustomerID = @CustomerID
AND [snapshot-date] >= DATEADD(MONTH, -1, @Enddate)
AND [snapshot-date] <= @Enddate
GROUP BY a.CustomerID ,
b.DevIDSellerId ,
a.OrderSourceID,
a.sku
)
SELECT
os.OrderSourceName AS [账号],
(
CASE
poss.[isDelete]
WHEN '1' THEN '不在线'
ELSE '在线'
END
) AS [在线状态],
(
CASE
possa.[rowid]
WHEN '2' THEN 'EFN'
ELSE '非EFN'
END
) AS [EFN],
os.OrderSourceCountry AS [站点],
RB_BusinessTeam.BusinessTeamName AS [销售组],
os.SaleAdminName AS [账号首选销售人员],
a.UserName AS [listing销售人员],
rep.[asin] AS [ASIN],
rep.sku AS [渠道SKU],
pro.SKU AS [系统SKU],
pro.ClientSKU AS [自定义SKU],
pro.ProductNameCN AS [产品中文名],
ISNULL(c1.ClassNameCn, '') AS [产品一级类别中文名],
(
CASE
WHEN pro.ProductState = 0 THEN '正常'
WHEN pro.ProductState = 1 THEN '起批量'
WHEN pro.ProductState = 2 THEN '停产'
WHEN pro.ProductState = 3 THEN '锁定'
WHEN pro.ProductState = 4 THEN '暂时缺货'
WHEN pro.ProductState = 5 THEN '清库'
ELSE ''
END
) AS '产品状态',
(
CASE
pro.IsGroup
WHEN '1' THEN rep.[total-quantity] * (GroupProduct.SumLastSupplierPrice)
ELSE rep.[total-quantity] * (pro.LastSupplierPrice)
END
) AS [库存总额],
(
CASE
pro.IsGroup
WHEN '1' THEN GroupProduct.SumLastSupplierPrice
ELSE pro.LastSupplierPrice
END
) AS [最新采购价],
rep.[total-quantity] AS [库存总数量],
rep.[sellable-quantity] AS [库存可售数量],
rep.[unsellable-quantity] AS [库存不可售数量],
rep.[inv-age-0-to-90-days] AS [0-90天库龄数],
rep.[inv-age-91-to-180-days] AS [91-180天库龄数],
rep.[inv-age-181-to-270-days] AS [181-270天库龄数],
rep.[inv-age-271-to-365-days] AS [271-365天库龄数],
rep.[inv-age-365-plus-days] AS [大于365天库龄数],
(
rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]
) AS [超过90天总库龄数],
(
CASE
pro.IsGroup
WHEN '1' THEN (
(rep.[sellable-quantity]) * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
)
ELSE (
(rep.[sellable-quantity]) * (ISNULL(pro.LastSupplierPrice, 0))
)
END
) AS [可售金额],
(
CASE
pro.IsGroup
WHEN '1' THEN (
rep.[unsellable-quantity] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
)
ELSE (
rep.[unsellable-quantity] * (ISNULL(pro.LastSupplierPrice, 0))
)
END
) AS [不可售金额],
(
CASE
pro.IsGroup
WHEN '1' THEN (
rep.[inv-age-0-to-90-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
)
ELSE (
rep.[inv-age-0-to-90-days] * (ISNULL(pro.LastSupplierPrice, 0))
)
END
) AS [0-90天库存金额],
(
CASE
pro.IsGroup
WHEN '1' THEN (
rep.[inv-age-91-to-180-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
)
ELSE (
rep.[inv-age-91-to-180-days] * (ISNULL(pro.LastSupplierPrice, 0))
)
END
) AS [91-180天库存金额],
(
CASE
pro.IsGroup
WHEN '1' THEN (
rep.[inv-age-181-to-270-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
)
ELSE (
rep.[inv-age-181-to-270-days] * (ISNULL(pro.LastSupplierPrice, 0))
)
END
) AS [181-270天库存金额],
(
CASE
pro.IsGroup
WHEN '1' THEN (
rep.[inv-age-271-to-365-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
)
ELSE (
rep.[inv-age-271-to-365-days] * (ISNULL(pro.LastSupplierPrice, 0))
)
END
) AS [271-365天库存金额],
(
CASE
pro.IsGroup
WHEN '1' THEN (
rep.[inv-age-365-plus-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
)
ELSE (
rep.[inv-age-365-plus-days] * (ISNULL(pro.LastSupplierPrice, 0))
)
END
) AS [超过365天库存金额],
(
CASE
pro.IsGroup
WHEN '1' THEN (
(
rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]
) * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
)
ELSE (
(
rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]
) * (ISNULL(pro.LastSupplierPrice, 0))
)
END
) AS [超过90天总金额],
rep.[snapshot-date] AS [报告时间],
rep.[AddTime] AS [ERP下载时间]
FROM
(
SELECT
rep.CustomerID,
rep.OrderSourceID,
DevIDSellerId,
[asin],
rep.sku,
rep.[snapshot-date],
[AddTime],
[total-quantity],
[sellable-quantity],
[unsellable-quantity],
[inv-age-0-to-90-days],
[inv-age-91-to-180-days],
[inv-age-181-to-270-days],
[inv-age-271-to-365-days],
[inv-age-365-plus-days]
FROM
(
SELECT
*
FROM
(
SELECT
t.CustomerID,
t.DevIDSellerId,
t.OrderSourceID,
t.[snapshot-date],
t.sku,
ROW_NUMBER() OVER (
PARTITION BY t.CustomerID,
t.DevIDSellerId,t.sku
ORDER BY
t.[snapshot-date] DESC
) rowid
FROM t1 t
) a
WHERE
a.rowid = 1
) temp
INNER JOIN RB_Report_FULFILLMENT_INVENTORY_MANAGE rep (NOLOCK) ON temp.[snapshot-date] = rep.[snapshot-date]
AND temp.OrderSourceID = rep.OrderSourceID
AND temp.CustomerID = rep.CustomerID
AND temp.sku = rep.sku
) rep
INNER JOIN (
SELECT
t.CustomerID,
t.DevIDSellerId,
t.OrderSourceID,
t.ASINorItemID,
t.OrderSourceSKU,
t.SKU,
t.BusinessAdminID,
t.[isDelete]
FROM
(
SELECT
a.CustomerID,
c.DevIDSellerId,
a.OrderSourceID,
ASINorItemID,
a.OrderSourceSKU,
a.SKU,
a.BusinessAdminID,
a.[isDelete],
ROW_NUMBER() OVER (
PARTITION BY a.CustomerID,
c.DevIDSellerId,
ASINorItemID,
OrderSourceSKU
ORDER BY
a.AddTime DESC
) rowid
FROM
dbo.RB_Product_OrderSourceSKU a (NOLOCK)
INNER JOIN dbo.RB_OrderSource b (NOLOCK) ON b.CustomerID = a.CustomerID
AND b.ID = a.OrderSourceID
INNER JOIN dbo.RB_OrderSource_EbayAmazonAPI c (NOLOCK) ON c.CustomerID = a.CustomerID
AND c.OrderSourceID = a.OrderSourceID
INNER JOIN #tb_1 ON a.OrderSourceSKU = #tb_1.sku
WHERE
a.CustomerID = @CustomerID
AND b.OrderSourceType = 1
AND b.IsActive = 1
AND a.ASINorItemID <> ''
AND a.OrderSourceID IN (5927,5955,5985,6034,6057,6077,6096,6115,6155)
) t
WHERE
rowid = 1
) poss ON poss.OrderSourceSKU = rep.sku
AND poss.CustomerID = rep.CustomerID
AND poss.DevIDSellerId = rep.DevIDSellerId
AND poss.ASINorItemID = rep.asin
left JOIN (
SELECT
t.CustomerID,
t.DevIDSellerId,
t.OrderSourceID,
t.ASINorItemID,
t.OrderSourceSKU,
t.SKU,
t.rowid
FROM
(
SELECT
a.CustomerID,
c.DevIDSellerId,
a.OrderSourceID,
a.ASINorItemID,
a.OrderSourceSKU,
a.SKU,
ROW_NUMBER () OVER (
PARTITION BY a.CustomerID,
c.DevIDSellerId,
a.ASINorItemID,
OrderSourceSKU
ORDER BY
a.AddTime DESC
) rowid
FROM
dbo.RB_Product_OrderSourceSKU a (NOLOCK)
INNER JOIN dbo.RB_OrderSource b (NOLOCK) ON b.CustomerID = a.CustomerID
AND b.ID = a.OrderSourceID
INNER JOIN dbo.RB_OrderSource_EbayAmazonAPI c (NOLOCK) ON c.CustomerID = a.CustomerID
AND c.OrderSourceID = a.OrderSourceID
INNER JOIN #tb_1 ON a.OrderSourceSKU = #tb_1.sku
WHERE
a.CustomerID = @CustomerID
AND b.OrderSourceType = 1
AND b.IsActive = 1
AND a.ASINorItemID <> ''
AND a.OrderSourceID IN (5927,5955,5985,6034,6057,6077,6096,6115,6155)
) t
WHERE
rowid = 2
) possa ON possa.OrderSourceSKU = rep.sku
AND possa.CustomerID = rep.CustomerID
AND possa.DevIDSellerId = rep.DevIDSellerId
AND possa.ASINorItemID = rep.asin
LEFT JOIN (
SELECT
*
from
(
SELECT
Row_Number() OVER (
partition by o.CustomerID,
api.DevIDSellerId,
oi.SellerSKU
ORDER BY
shiptime desc
) as r,
o.CustomerID,
api.DevIDSellerId,
o.OrderSourceID,
o.OrderCode,
oi.SellerSKU as OrdersourceSKU,
o.ShipTime
from
RB_OrderInfo(nolock) o
inner join RB_OrderList(nolock) oi on o.CustomerID = oi.CustomerID
and o.OrderCode = oi.OrderCode
inner join RB_WareHouse (nolock) w on o.CustomerID = w.CustomerID
and o.ToWareHouseID = w.ID
inner join RB_OrderSource_EbayAmazonAPI(nolock) api on o.CustomerID = api.CustomerID
and o.OrderSourceID = api.OrderSourceID
where
o.IsStockOrder = 1 --取备库订单
and w.WareHouseType = 3 --到货仓取FBA仓
and o.OrderState != 0 --取发货的订单
) t
where
t.r = 1
) possb on rep.CustomerID = possb.CustomerID
and rep.DevIDSellerId = possb.DevIDSellerId
and rep.sku = possb.OrdersourceSKU
LEFT JOIN RB_Admin a (NOLOCK) ON a.CustomerID = poss.CustomerID
AND a.ID = poss.BusinessAdminID
LEFT JOIN RB_OrderSource os (NOLOCK) ON os.CustomerID = poss.CustomerID
AND os.ID = possb.OrderSourceID
LEFT JOIN RB_Admin (NOLOCK) ON RB_Admin.CustomerID = os.CustomerID
AND RB_Admin.ID = os.SaleAdminID
LEFT JOIN RB_BusinessTeam (NOLOCK) ON RB_BusinessTeam.CustomerID = os.CustomerID
AND RB_BusinessTeam.ID = RB_Admin.BusinessTeamID
LEFT JOIN RB_Product pro (NOLOCK) ON pro.CustomerID = poss.CustomerID
AND pro.SKU = poss.SKU
LEFT JOIN RB_Product_Class (NOLOCK) c1 ON pro.CustomerID = c1.CustomerID
AND pro.ClassID1 = c1.ID
LEFT JOIN (
SELECT
b.GroupSku,
b.CustomerID,
SUM(c.LastSupplierPrice * b.SkuNum) AS SumLastSupplierPrice
FROM
RB_Product_GroupSku b (NOLOCK)
LEFT JOIN RB_Product c (NOLOCK) ON c.SKU = b.SKU
AND c.CustomerID = b.CustomerID
WHERE
b.CustomerID = @CustomerID
GROUP BY
b.GroupSku,
b.CustomerID
) GroupProduct ON GroupProduct.GroupSku = pro.SKU
AND GroupProduct.CustomerID = pro.CustomerID ) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 20
PARAMS:@Enddate = 2022-03-01 23:59:59 ,@CustomerId = 1 ,
MESSAGE:关键字 'IF' 附近有语法错误。
“)”附近有语法错误。
TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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, TaskCompletionSource`1 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.ExecuteDbDataReader(CommandBehavior behavior)
在 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)
在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB)
在 RB.DALExtend.RB_CustomReportExtend.GetPageDataNoTotalCount(String sqlContent, List`1 sqlParameters, CM_GetCustomReportPageDataParam pager, String orderBy, Int32 timeOut)
在 Biz.Irobotbox.ReportCentre.CustomReportService.GetPageDataForCustomReport(Int32 AdminID, Int32 customerId, Int32 customReportId, Int32 templateId, Dictionary`2 paramList, CM_GetCustomReportPageDataParam pager, String orderBy, Int32 timeOut, Boolean isGetTotalCount)
在 Biz.Irobotbox.ReportCentre.CustomReportService.GetPageDataForCustomReport(Int32 AdminID, Int32 customerId, Int32 customReportId, Int32 templateId, Dictionary`2 paramList, Pager pager, String orderBy, Boolean isGetTotalCount)
在 PandaBuy.ERP.Web.IrobotBox.ReportCentre.CustomReportExportList.GetPageData()
在 PandaBuy.ERP.Web.IrobotBox.ReportCentre.CustomReportExportList.Page_Load(Object sender, EventArgs e)
在 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
在 System.Web.UI.Control.OnLoad(EventArgs e)
在 System.Web.UI.Control.LoadRecursive()
在 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
在 System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
在 System.Web.UI.Page.ProcessRequest()
在 System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
在 System.Web.UI.Page.ProcessRequest(HttpContext context)
在 ASP.irobotbox_reportcentre_customreportexportlist_aspx.ProcessRequest(HttpContext context)
在 System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
在 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
在 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
在 System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
在 System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
在 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
-------------------------------------------------------------
[期望]
正常预览
功能原设计，应用到临时表不用支持预览