[步骤]

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)

-------------------------------------------------------------

   

[期望]