tfenwick11's questions

下面的查询具有以字符串形式返回的数据集的固定长度值，现在的问题是查询结果中不支持固定长度。但是 DB 邮件生成的输出文本文件看起来是正确的。请参阅以下示例：

     SELECT 
          LEFT(CONVERT(char(9), machine) + SPACE(9),9) 
          + LEFT(CONVERT(char(30), part_no) + SPACE(30), 30) 
          + LEFT(CONVERT(char(7), ordered) + SPACE(7), 7) 
          + LEFT(CONVERT(char(7), picked)  + SPACE(7), 7) 
          + LEFT(CONVERT(char(7), allocated)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), in_stock)  + SPACE(7), 7) 
          + LEFT(CONVERT(char(7), sh_bin_qty)  + SPACE(7), 7)  
          + LEFT(CONVERT(char(7), short)  + SPACE(7), 7) 
          + min_bin

原始输出结果

文本文件结果

目前，我的任务是修改一个超级旧的存储过程，它表示我们的 SQL 2000 实例中的电子邮件输出。通过更正一些语法问题，我希望原始字符串格式在电子邮件中更能体现出来。因为字段“机器”和“零件号”具有字段数据长度的差异，因此字段会移动并且列不会完全对齐。我一直在尝试的是将我的 SQL 代码中存在的每个字段设置为硬设置值，例如 15 个字符，这样每个字段的格式不会改变/转移，并且直接面向最终用户。现在我在网上尝试了一些建议，但找不到设置每个字段的方法。有任何想法吗？

 INSERT INTO ##prod_performance_txt_out2
               SELECT LEFT(machine + '             ', 9) 
          + LEFT(part_no + '                              ', 30) 
          + LEFT(CONVERT(varchar(7), ordered) + '       ', 7) 
          + LEFT(CONVERT(varchar(7), picked)  + '       ', 7) 
          + LEFT(CONVERT(varchar(7), allocated)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), in_stock)  + '       ', 7) 
          + LEFT(CONVERT(varchar(7), sh_bin_qty)  + '       ', 7)  
          + LEFT(CONVERT(varchar(7), short)  + '       ', 7) 
          + min_bin


               FROM #tempsummary2 WHERE resource = @resource AND short > 0

目前在水晶报表中，我将每个订单组合在一起，向用户显示订单组中的订单行。我已经为所有订单行价格的小计制作了一个运行总计字段。但我不想为最终用户设置一个参数来选择价格是否大于或小于特定数量。我认为最好的解决方案可能是在存储过程中计算小计并将其传递给报表以构建参数。ROLLUP不过好像SQL Server 2000以后的版本没有这个功能了。

在尝试将水晶报表公式转换为 SQL CASE 表达式时，我似乎无法理解“total_pallet_weight”和“order_no”的概念，利用公式中的“AND”和“cubic feet”和“order_no”。我想通过下面的尝试，我会从一个仅用于“total_pallet_weight”的 CASE 开始，然后有一个立方英尺的子 CASE 来表示公式中的“AND”。但我不熟悉语法“({a_ras_shipping_order_PCF_vw.total_pallet_weight}, {a_ras_truck_shipment_vw.order_no}) > 0”的工作原理。forumla 是否说“托盘总重量 + order_no > 0”？

水晶报表公式：

IF Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight}, 
{a_ras_truck_shipment_vw.order_no}) > 0
AND Sum ({a_ras_shipping_order_PCF_vw.cubic_feet}, 
{a_ras_truck_shipment_vw.order_no}) > 0
THEN
Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight}, 
{a_ras_truck_shipment_vw.order_no})
/Sum ({a_ras_shipping_order_PCF_vw.cubic_feet}, 
{a_ras_truck_shipment_vw.order_no})
ELSE 0

尝试替换公式：

CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0
     THEN
        ,CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * 
         TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0 
             THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) + 
             (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * 
             TPM.dim_ext_x * TPM.dim_ext_y) / 1728)))
             ELSE 0
        END
     ELSE 0  
  END AS 'Total_PCF'

“托盘总重量”的语法

SUM (TCT.[weight]) + SUM(TPM.[weight]) AS 'Total_Pallet_Weight'

“立方英尺”的语法

CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND 
TPM.dim_ext_y > 0
THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * 
TPM.dim_ext_y) / 1728)
ELSE 0 END AS 'cubic_feet',

好的，我知道有数百篇文章说明了如何将参数从存储过程传递到水晶报表。但不幸的是，通过一些研究，我没有找到关于 SQL Server 变量和水晶报表参数的问题的具体答案。

问题：如果我有一个存储过程，并且我声明了一个名为“位置”的变量，并且我想在我的水晶报表中引用该变量作为新参数，并且最终用户将看到一个对话框，允许他们从不同的位置进行选择这将返回仅针对从下拉列表中选择的特定位置的结果。那怎么会发生？

目前尝试的方法：

水晶报表生成器 11

1. 在字段资源管理器下的“参数字段”中，选择新建，然后按动态选择“值列表”，然后选择与我的变量一起使用的数据源和列，它说“参数”是@location。但是当我运行报告时，我会看到一个位置值对话框，但没有可供选择的值。（要注意位置字段是由用户通过报告下拉选择提供的，从而从存储过程返回结果）

2. 在“参数字段”中选择新的值，然后将值列表保留为“静态”，并选择值字段作为“位置”列，并输入与位置有关的多个值供用户选择。（这确实会向用户显示一个对话框，建议一个位置的下拉值，但没有返回结果，所以我不确定这是否是正确的方法？）

代码：

ALTER PROCEDURE [dbo].[a_ras_TruckSummary_SP] 

AS
BEGIN

IF (object_id('tempdb..#tempTruckSummary') IS NOT NULL)
BEGIN
    DROP TABLE #tempTruckSummary
END


DECLARE @location VARCHAR(10)


 SELECT O.order_no, L.shipped, L.price, L.[location] FROM orders O WITH 
(NOLOCK) 
INNER JOIN Ord_list L ON O.order_no = L.order_no
    WHERE O.routing NOT LIKE 'FEDX%' 
    AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3') 
    AND ISNULL(O.void,'') <> 'V'
    AND L.location = @location
    GROUP BY O.order_no, L.shipped, L.price, L.[location]

使用下面的代码，我最初试图不使用子查询来消除“重复数据”（它在技术上不是重复数据，因为每个订单都有几行价格和重量不同的数据，但我想将我的结果合并到我只获得不同的订单号）但是没有运气只使用连接条件和 SUM/GROUP BY。我在第一次加入子查询时采用了返回带有“价格”和“已发货”字段的不同订单。并且相应地工作，我收到了我需要的订单。但是通过使用整个脚本，我意识到“carton_no”导致返回“重复”结果，所以我尝试添加另一个子查询，但我似乎收到了错误：

消息 107，级别 16，状态 2，第 17 行列前缀“TCT”与查询中使用的表名或别名不匹配。

提供错误的代码块：

LEFT OUTER JOIN 
  (
       SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK) 
       INNER JOIN tdc_carton_tx TCT 
       ON TRCB.order_no = TCT.order_no
       AND TRCB.order_ext = TCT.order_ext
       AND TRCB.carton_no = TCT.carton_no   
       GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
   ) TRCB ON  TRCB.order_no = TCT.tdc_carton_tx 

DECLARE @location VARCHAR(10)
DECLARE @dateshipped DATETIME 
SET @location = 'RAS 2'
SET @DateShipped = DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())-3)


SELECT  

      SUM (L.Shipped ) AS Shipped
    , SUM (L.Price ) AS Price
    , O.Order_no
    , O.Ext
    , O.Cust_Code 
    , O.Ship_To_Name 
    , O.Freight_Allow_Type 
    , TCT.Carton_no 
    , TPM.dim_ext_x  
    , TPM.dim_ext_y 
    , TRCB.Number_of_Boxes 
    , MAX(TRCB.Skid_Height_Inches )
    , MAX(O.Routing) AS 'Routing'  
    , MAX(O.User_def_fld9) AS 'bol_no'  
    , MAX(Ship_to_name) AS Ship_to_name_1 
    , MAX(Ship_to_add_3) AS Ship_to_add_3 
    , MAX(L.[location]) AS 'Location' 
    , MAX(O.cust_code ) AS 'Cust_code_1' 
    , MAX(O.user_def_fld10) AS 'Skids' 
    , MAX(O.user_def_fld12) AS 'Weight' 
    , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_Freight'
    , TCT.[weight] + TPM.[weight] AS 'Total_Pallet_Weight'




FROM orders O WITH (NOLOCK)
INNER JOIN  
    (
       SELECT  o.order_no, L.shipped, L.price FROM orders O WITH (NOLOCK) INNER JOIN Ord_list L ON O.order_no = L.order_no
        WHERE O.routing NOT LIKE 'FEDX%' 
        AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3') 
        AND ISNULL(O.void,'') <> 'V'
        AND L.location = @location
        GROUP BY O.order_no, L.shipped, L.price
   )
   L ON O.order_no = L.order_no
JOIN xfers X
     ON O.order_no = X.xfer_no
JOIN xfer_list XL
    ON  X.xfer_no = XL.xfer_no
--INNER JOIN ord_list L WITH (NOLOCK)
--  ON  O.order_no = L.order_no
--     AND O.ext = L.order_ext
INNER JOIN arcust C WITH (NOLOCK)
    ON O.cust_code = C.customer_code 
LEFT OUTER JOIN armaster A WITH (NOLOCK)
    ON A.customer_code = O.cust_code  
       AND A.ship_to_code = O.ship_to
       AND A.location_code = O.location  
LEFT OUTER JOIN tdc_pkg_master TPM WITH (NOLOCK)
     ON TCT.carton_type=TPM.pkg_code
INNER JOIN tdc_carton_tx TCT WITH (NOLOCK)
     ON TPM.pkg_code = TCT.carton_type
LEFT OUTER JOIN 
  (
       SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK) 
       INNER JOIN tdc_carton_tx TCT 
       ON TRCB.order_no = TCT.order_no
       AND TRCB.order_ext = TCT.order_ext
       AND TRCB.carton_no = TCT.carton_no   
       GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
   ) TRCB
        ON  TRCB.order_no = TCT.tdc_carton_tx 
LEFT OUTER JOIN ras_bol_details BD WITH (NOLOCK)
    ON X.xfer_no = BD.bl_src_no
       AND X.xfer_no = BD.bl_src_no AND BD.order_type IN ('T','S')
LEFT OUTER JOIN ras_bol_header BH WITH (NOLOCK)
    ON BD.bl_no = BH.bl_no
       AND O.order_no = BD.bl_src_no 
       AND O.ext = BD.bl_src_ext 

WHERE  DATEADD(day, 0, DATEDIFF(day, 0, O.date_shipped)) >= @dateshipped




GROUP BY

      O.order_no
    , O.ext
    , O.cust_code
    , O.ship_to_name 
    , O.freight_allow_type 
    , TCT.carton_no 
    , TCT.[weight]
    , TPM.[weight]
    , TPM.dim_ext_x  
    , TPM.dim_ext_y 
    , TRCB.number_of_boxes 
    , TRCB.skid_height_inches 

目前，我正在尝试将三个视图组合到一个存储过程中以生成报告。起初，我开始对公共数据字段使用连接，但发现其中一个表 ,XFER包含导致重复数据的数据。所以我认为最好的做法是获取所有三个视图并仅将我需要的数据插入到一个临时表中。这样做的原因是每个视图都引用一个公共列order_no，并且在一个结果集中的所有三个视图我想按顺序明确定义一个结果集，并且在使用连接时没有重复值。

我的问题：是否可以使用所有三个结果集中的选定列定义一个临时表，并将它们合并到一个表中，尽管它们共享order_no？我尝试了两种方法：

1. 首先创建一个临时表，其中包含我想从所有三个查询集中呈现的所有列，然后从每个查询中插入 INSERT INTO 以获得组合结果

2. 不要直接定义一个临时表，并让第一个 SELECT INTO 临时表，然后剩下的两个查询使用 INSERT INTO

到目前为止，在尝试了这两种方法后，我收到了消息：

插入错误：列名或提供的值数与表定义不匹配。

    IF (object_id('tempdb..#TruckSummary') IS NOT NULL)
    BEGIN
        DROP TABLE #TruckSummary
    END

    CREATE TABLE #TruckSummary
    (
        order_no int,
        freight_allow_type varchar(10),
        shipped decimal(20, 8),
        carton_no int,
        number_of_boxes int,
        skid_height_inches int,
        dim_ext_x float,
        dim_ext_y float,
        product_weight float,
        pallet_weight float,
        total_pallet_weight float,
        cubic_feet decimal(10, 4),
        PCF decimal(10, 4),
        routing varchar(20),
         bol_no int,
         ext int,
         ship_to_name varchar(40),
         ship_to_add_3 varchar(40),
         [location] varchar(10),
         cust_code varchar(10),
         date_shipped varchar(20),
         skids int,
         [weight] float,
         BOL_est_freight decimal(10, 2) 
    )




INSERT   INTO #TruckSummary


SELECT 
      O.order_no
    , O.freight_allow_type
    , L.shipped

FROM orders O (NOLOCK)
INNER JOIN ord_list L (NOLOCK)
    ON O.order_no = L.order_no 
       AND O.ext = L.order_ext AND ISNULL(L.void,'') <> 'Y'
INNER JOIN arcust C (NOLOCK)
    ON O.cust_code = C.customer_code
LEFT OUTER JOIN armaster A (NOLOCK)
    ON A.customer_code = O.cust_code
       AND A.ship_to_code = O.ship_to
WHERE O.status < 'S'
       AND ISNULL(O.void,'') <> 'V'
       AND O.date_shipped IS NULL

GROUP BY O.order_no, O.freight_allow_type, L.shipped 




INSERT INTO #TruckSummary

SELECT 
   TCT.order_no,
   TCT.carton_no, 
   TRCB.number_of_boxes, 
   TRCB.skid_height_inches, 
   TPM.dim_ext_x, 
   TPM.dim_ext_y, 
   TCT.weight AS 'product_weight',
   TPM.weight AS 'pallet_weight', 
   TCT.weight + TPM.weight AS 'total_pallet_weight',

   CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0
        THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728)
   ELSE 0 END AS 'cubic_feet',

   CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0 
     ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
        THEN CONVERT(DECIMAL(10,4), (TCT.weight + TPM.weight) / ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
   ELSE 0 END AS 'PCF',
   TCT.last_modified_date,
   TCT.modified_by,
   TCT.order_type
FROM   pltjones.dbo.tdc_carton_tx TCT (NOLOCK)
LEFT OUTER JOIN pltjones.dbo.tdc_revshelf_carton_box TRCB 
   ON TCT.order_no=TRCB.order_no 
      AND TCT.order_ext=TRCB.order_ext 
      AND TCT.carton_no=TRCB.carton_no 
LEFT OUTER JOIN pltjones.dbo.tdc_pkg_master TPM 
   ON TCT.carton_type=TPM.pkg_code
WHERE ISNULL(TCT.date_shipped,GETDATE()) > DATEADD(mm, -6, getdate())




INSERT INTO #TruckSummary

SELECT 
     MAX(O.routing) AS 'routing'
    , MAX(O.user_def_fld9) AS 'bol_no'
    , O.order_no 
    , O.ext 
    , MAX(ship_to_name) AS ship_to_name
    , MAX(ship_to_add_3) AS ship_to_add_3
    , MAX(OL.location) AS 'location'
    , MAX(O.cust_code ) AS 'cust_code'
    , CONVERT(DATETIME, CONVERT(varchar(20) , MAX(O.date_shipped), 23)) AS 'date_shipped'
    , MAX(O.user_def_fld10) AS 'skids'
    , MAX(O.user_def_fld12) AS 'weight'
    , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_freight'
FROM orders O (NOLOCK)
     INNER JOIN ord_list OL (NOLOCK)
        ON O.order_no = OL.order_no 
             AND O.ext = OL.order_ext
    INNER JOIN ras_bol_details BD (NOLOCK)
        ON O.order_no = BD.bl_src_no 
             AND O.ext = BD.bl_src_ext 
             AND BD.order_type = 'S'
    LEFT OUTER JOIN ras_bol_header BH (NOLOCK)
        ON BH.bl_no = BD.bl_no
WHERE O.date_shipped >= dateadd(dd, -30, getdate())
             AND O.routing NOT LIKE 'FEDX%'
             AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
GROUP BY O.order_no, O.ext

UNION

SELECT TOP 100 PERCENT
    BH.routing
    , BD.bl_no
    , X.xfer_no
    , BD.bl_src_ext AS ext
    , X.to_loc_name
    , X.to_loc_addr3
    , X.from_loc
    , 'cust_no'
    , CONVERT(varchar(20) , X.date_shipped, 23)
    , BD.skids
    , BD.tare_wt
    , BH.est_freight_cost
FROM xfers X (NOLOCK)
JOIN xfer_list XL (NOLOCK)
    ON X.xfer_no = XL.xfer_no
JOIN ras_bol_details BD (NOLOCK)
    ON X.xfer_no = BD.bl_src_no 
       AND BD.order_type = 'T'
JOIN ras_bol_header BH (NOLOCK)
    ON BD.bl_no = BH.bl_no
WHERE X.to_loc IN ('KM', 'AWNC', 'KMUT', 'AWAZ', 'SM')
  AND X.date_shipped >  dateadd(dd, -30, getdate())

我知道有几篇关于从日期时间列中删除时间部分的帖子。并且每个人都建议 DATEADD 和 DATEDIFF 方法最适合性能，个人不应该采用将日期值转换/转换为 varchar 的过程。所以我想利用下面的函数，但我不明白为什么我收到的结果仍然有与之关联的时间值，尽管时间全为零？

提供有关我为什么要了解此结果的更多信息。我有一个存储过程，我希望指定以下内容：

DECLARE @DateShippedBegin DATETIME
DECLARE @DateShippedEnd DATETIME
SET @DateShippedBegin = dateadd(dd, datediff(dd, 0, @DateShipped), 0)
SET @DateShippedEnd = dateadd(dd, datediff(dd, 0, @DateShipped) + 1, 0)

WHERE O.date_shipped >= @DateShippedBegin and O.date_shipped < @DateShippedEnd

但是，当我使用基本日期格式执行该程序时，我没有收到任何结果，因此试图了解我的程序采用的最佳概念。

Select DATEADD(dd, DATEDIFF(dd, 0, Getdate()), 0) from orders where order_no = 
'1247401'

起始日期值示例：

2017-05-010 08:40:18.287

查询结果示例：

2017-05-10 00:00:00.000

目前，我想通过声明一个变量来测试我的新代码，以根据字段“DateShipped”的当前日期返回结果具有设定值。但是通过研究我注意到 SQL Server 2000 直到 2008 年才获得“GETDATE()”，因此需要 DATETIME。对于我尝试的每种不同的转换格式，尽管今天有可用的当前记录，但我似乎无法返回一组结果。任何帮助，将不胜感激。

日期时间列值：2016-05-19 08:40:18.287

代码部分有问题：

声明@date_shipped DATETIME

SET @date_shipped = dateadd(dd,datediff(dd,0,getdate()),0)

 WHERE  DATEADD(dd, 0, DATEDIFF(dd, 0, O.date_shipped)) = @date_shipped

***代码如下：

DECLARE @date_shipped DATETIME 
        SET @date_shipped = dateadd(dd,datediff(dd,0,getdate()),0)
--@location VARCHAR(10)
--SET @location = 'RAS 2'
--SET @date_shipped = CONVERT(varchar(10), @date_shipped, 23)




SELECT  
      O.Date_Shipped
    , L.Shipped  
    , L.Price 
     , O.Order_no
    , O.Ext
    , O.Cust_Code 
    , O.Ship_To_Name 
    , O.Freight_Allow_Type 
    , TCT.Carton_no 
     , TPM.dim_ext_x  
     , TPM.dim_ext_y 
    , TRCB.Number_of_Boxes 
     , TRCB.Skid_Height_Inches 
    , MAX(O.Routing) AS 'Routing'  
    , MAX(O.User_def_fld9) AS 'bol_no'  
    , MAX(Ship_to_name) AS Ship_to_name_1 
    , MAX(Ship_to_add_3) AS Ship_to_add_3 
    , MAX(L.[location]) AS 'Location' 
    , MAX(O.cust_code ) AS 'Cust_code_1' 
    --, CONVERT(DATETIME, CONVERT(varchar(20) , MAX(O.date_shipped), 23)) AS 'date_shipped'
    , MAX(O.user_def_fld10) AS 'Skids' 
    , MAX(O.user_def_fld12) AS 'Weight' 
    , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_Freight'
     , TCT.[weight] + TPM.[weight] AS 'Total_Pallet_Weight'

---->>>>>Formula "CUBIC FEET"
    ,CASE 
        WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0
            THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728) 
                 ELSE 0 END AS 'Cubic_Feet' 
---->>>>>Formula "CUBIC FEET"


---->>>>>Formula "PCF"
    , CASE 
        WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0 
            THEN CONVERT(DECIMAL(10,4), (TCT.weight + TPM.weight) / ((TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))
                ELSE 0 END AS 'PCF'
---->>>>>Formula "PCF"


---->>>>>Formula "TOTAL_PCF"
    , CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0
         THEN
            CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0 
                 THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) + (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728)))
                 ELSE 0
            END
         ELSE 0  
      END AS 'Total_PCG'
---->>>>>Formula "TOTAL_PCF"


---->>>>>Formula "SHIP TOTAL"
    , CASE 
         WHEN (L.shipped * L.Price > 0 ) 
            THEN (L.Shipped * L.Price)
                ELSE 0 
                    END AS 'ShipTotal'
---->>>>>Formula "SHIP TOTAL"


------>>>>>>>>TEMP TABLE
--INTO #tempTruckSummary
------>>>>>>>>TEMP TABLE

FROM xfers X WITH (NOLOCK)

INNER JOIN xfer_list XL
    ON  X.xfer_no = XL.xfer_no
INNER JOIN orders O WITH (NOLOCK)
    ON X.xfer_no = O.order_no
INNER JOIN ord_list L
    ON  O.order_no = L.order_no
       AND O.order_no = L.order_no 
       AND O.ext = L.order_ext
       AND X.xfer_no = XL.xfer_no
INNER JOIN arcust C WITH (NOLOCK)
    ON O.cust_code = C.customer_code 
LEFT OUTER JOIN armaster A WITH (NOLOCK)
    ON A.customer_code = O.cust_code  
       AND A.ship_to_code = O.ship_to
       AND A.location_code = O.location  
INNER JOIN tdc_carton_tx TCT WITH (NOLOCK)
     ON O.order_no = TCT.order_no
LEFT OUTER JOIN tdc_revshelf_carton_box TRCB WITH (NOLOCK)
    ON TCT.order_no=TRCB.order_no 
       AND TCT.order_ext=TRCB.order_ext 
       AND TCT.carton_no=TRCB.carton_no   
LEFT OUTER JOIN tdc_pkg_master TPM WITH (NOLOCK)
     ON TCT.carton_type=TPM.pkg_code
JOIN ras_bol_details BD WITH (NOLOCK)
    ON X.xfer_no = BD.bl_src_no
       AND X.xfer_no = BD.bl_src_no AND BD.order_type IN ('T','S')
JOIN ras_bol_header BH WITH (NOLOCK)
    ON BD.bl_no = BH.bl_no
       AND O.order_no = BD.bl_src_no 
       AND O.ext = BD.bl_src_ext 


WHERE  DATEADD(dd, 0, DATEDIFF(dd, 0, O.date_shipped)) = @date_shipped
       AND X.to_loc IN ('KM', 'AWNC', 'KMUT', 'AWAZ', 'SM')
       AND O.routing NOT LIKE 'FEDX%'
       AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
       AND ISNULL(O.void,'') <> 'V'
     --  AND L.Location = @Location


GROUP BY
      O.date_shipped
    , L.shipped  
    , L.Price 
     , O.order_no
    , O.ext
    , O.cust_code 
    , O.ship_to_name 
    , O.freight_allow_type 
    , TCT.carton_no 
    , TCT.[weight]
    , TPM.[weight]
     , TPM.dim_ext_x  
     , TPM.dim_ext_y 
    , TRCB.number_of_boxes 
     , TRCB.skid_height_inches 

通过运行下面的整个 T-SQL 代码，我似乎在下面收到以下错误。

故障排除步骤：

1. 在每个“UNION”之前运行每个代码块（每个块执行良好并返回结果）
2. 检查了每个块的所有语法，没有发现下划线语法错误
3. 执行包含“PartTran”的部分的最后三个代码块，并且三个块正确执行。
4. 执行前两个代码块并提供错误

我对这个错误感到很困惑，因为如果我使用第一段代码并且我在选择列表中的一行并按特定列的列表分组，所有代码都会运行。据我所知，我为每个代码块选择并分组了相同的列，但是当一起运行所有块时，我收到错误。任何帮助，将不胜感激！

执行错误：

消息 205，级别 16，状态 1，第 2 行所有使用 UNION、INTERSECT 或 EXCEPT 运算符组合的查询在其目标列表中必须具有相同数量的表达式。

    SELECT 
             OH.OrderStatus
            ,OH.ShipByTime
            ,CS.PriceTolerance
            ,OH.OrderNum
            ,OH.ExtCompany
            ,CS.[Name]
            ,OH.TotalInvoiced - OH.TotalDiscount AS 'net_sales_order'
            ,OH.CustNum
            ,OH.ShipToNum
            ,OH.ShipToCustNum
            ,SUM(CAST(OD.OrderQty AS int)) AS QtySum
            ,COUNT(OD.OrderLine) AS LineCnt
    FROM erp.customer AS CS WITH (NOLOCK)
    INNER JOIN erp.orderhed AS OH WITH (NOLOCK)
        ON CS.custnum = OH.custnum
    LEFT JOIN erp.OrderDtl as OD
        ON OH.OrderNum = OD.OrderNum
            AND OH.ExtCompany = OD.ExtCompany
    WHERE (OH.VoidOrder <> '1') AND OH.OrderStatus BETWEEN 'N' AND 'Q' --order status is currently empty in E10LiveDB on RASE10PROD
    GROUP BY 
         OH.OrderStatus
        ,OH.ShipByTime
        ,CS.PriceTolerance
        ,OH.OrderNum
        ,OH.ExtCompany
        ,CS.[Name]
        ,OH.TotalInvoiced - OH.TotalDiscount
        ,OH.CustNum
        ,OH.ShipToNum
        ,OH.ShipToCustNum

    UNION

    SELECT --PT.STATUS
        PT.TranDate
        ,'CONSIGN' AS Expr1
        ,PT.PartNum
        ,0 AS 'ext'
        ,'KRAFTMAID' + ' - ' + PT.LotNum AS Expr2
        ,SUM(CONVERT(DECIMAL(10, 2), PT.Acttranqty * PT.MtlUnitCost)) AS Expr3
        ,PT.LotNum
        ,0 AS Expr4
        ,'KRAFTMAID' AS Expr5
        ,SUM(CAST(PT.Acttranqty AS int)) AS QtySum
        ,COUNT(PT.OrderLine) AS LineCnt
    FROM erp.PartTran AS PT WITH (NOLOCK)
    INNER JOIN erp.PartWhse AS PW WITH (NOLOCK)
        ON PT.PartNum = PW.PartNum
    INNER JOIN erp.PartLot AS PL WITH (NOLOCK)
        ON PT.PartNum = PL.PartNum
            AND PL.FirstRefDate < GETDATE()
            AND PL.ExpirationDate > GETDATE()
    LEFT JOIN erp.Customer AS C WITH (NOLOCK)
        ON PT.CustNum = C.CustNum
            AND C.CustID = '43360'
    WHERE (
            PT.LotNum IN (
                'KM'
                ,'KMUT'
                )
            )
    --  AND (X.STATUS < 'S')
    --  AND XL.shipped = 0
    GROUP BY --X.STATUS,
        PT.TranDate
        ,PT.PartNum
        ,PT.LotNum

    UNION

    SELECT -- X.STATUS
         PT.TranDate
        ,'CONSIGN' AS Expr1
        ,PT.PartNum
        ,0 AS 'ext'
        ,'AMERICAN WOODMARK' + ' - ' + PT.LotNum AS Expr2
        ,SUM(CONVERT(DECIMAL(10, 2), PT.Acttranqty * PT.MtlUnitCost)) AS Expr3
        ,PT.LotNum
        ,0 AS Expr4
        ,'AMERICAN WOODMARK' AS Expr5
        ,SUM(CAST( PT.Acttranqty AS int)) AS QtySum
        ,COUNT(PT.OrderLine) AS LineCnt
        FROM erp.PartTran AS PT WITH (NOLOCK)
    INNER JOIN erp.PartWhse AS PW WITH (NOLOCK)
        ON PT.PartNum = PW.PartNum
    INNER JOIN erp.PartLot AS PL WITH (NOLOCK)
        ON PT.PartNum = PL.PartNum
            AND PL.FirstRefDate < GETDATE()
            AND PL.ExpirationDate > GETDATE()
    LEFT JOIN erp.Customer AS C WITH (NOLOCK)
        ON PT.CustNum = C.CustNum
            AND C.CustID = '43360'
    WHERE (PT.LotNum = 'AWNC')
    --  AND (X.STATUS < 'S')
    --  AND XL.shipped = 0
    GROUP BY --X.STATUS,
        PT.TranDate
        ,PT.PartNum
        ,PT.LotNum

    UNION

    SELECT -- X.STATUS
        PT.TranDate
        ,'CONSIGN' AS Expr1
        ,PT.PartNum
        ,0 AS 'ext'
        ,'AMERICAN WOODMARK' + ' - ' + PT.LotNum AS Expr2
        ,SUM(CONVERT(DECIMAL(10, 2), PT.Acttranqty * PT.MtlUnitCost)) AS Expr3
        ,PT.LotNum
        ,0 AS Expr4
        ,'AMERICAN WOODMARK' AS Expr5
        ,SUM(CAST(PT.Acttranqty AS int)) AS QtySum
        ,COUNT(PT.OrderLine) AS LineCnt
        FROM erp.PartTran AS PT WITH (NOLOCK)
    INNER JOIN erp.PartWhse AS PW WITH (NOLOCK)
        ON PT.PartNum = PW.PartNum
    INNER JOIN erp.PartLot AS PL WITH (NOLOCK)
        ON PT.PartNum = PL.PartNum
            AND PL.FirstRefDate < GETDATE()
            AND PL.ExpirationDate > GETDATE()
    LEFT JOIN erp.Customer AS C WITH (NOLOCK)
        ON PT.CustNum = C.CustNum
            AND C.CustID = '11088'
    WHERE (PT.LotNum = 'AWAZ')
    --  AND (X.STATUS < 'S')
    --AND XL.shipped = 0
    GROUP BY -- X.STATUS,
        PT.TranDate
        ,PT.PartNum
        ,PT.LotNum

    UNION

    SELECT -- X.STATUS
        PT.TranDate
        ,'CONSIGN'
        ,PT.PartNum
        ,0 AS 'ext'
        ,'STAR MARK' + ' - ' + PT.LotNum
        ,SUM(CONVERT(DECIMAL(10, 2), PT.Acttranqty * PT.MtlUnitCost))
        ,PT.LotNum
        ,0
        ,'AMERICAN WOODMARK'
        ,SUM(CAST(PT.Acttranqty AS int)) AS QtySum
        ,COUNT(PT.OrderLine) AS LineCnt
            FROM erp.PartTran AS PT WITH (NOLOCK)
    INNER JOIN erp.PartWhse AS PW WITH (NOLOCK)
        ON PT.PartNum = PW.PartNum
    INNER JOIN erp.PartLot AS PL WITH (NOLOCK)
        ON PT.PartNum = PL.PartNum
            AND PL.FirstRefDate < GETDATE()
            AND PL.ExpirationDate > GETDATE()
    LEFT JOIN erp.Customer AS C WITH (NOLOCK)
        ON PT.CustNum = C.CustNum
            AND C.CustID IN
             (
                '49023'
                ,'11444'
                            )   
    WHERE PT.LotNum = 'SM'
    --  AND X.STATUS < 'S'
    --  AND XL.shipped = 0
    GROUP BY --X.STATUS,
        PT.TranDate
        ,PT.PartNum
        ,PT.LotNum
    GO

查看当前 SSRS 迁移的存储过程。我注意到下面列出的临时表 (#tempShip) 的 case 语法对临时表中的“ord_status”列没有影响。为临时表收集数据的存储过程相当庞大，因此我将避免发布。但我相信，通过为更新提供的代码，它可能会提供一些理由。

问题：

1. 您可以利用临时表上的 case 语句作为更新吗？

2. 除了案例陈述之外，我还应该尝试另一种方法吗？（我不认为使用带有“IF”或“WHERE EXISTS with a select statement”的子查询会对此有所帮助）

笔记：

***存储过程执行没有任何错误，临时表按预期填充数据（但使用 case 语句更新没有预期结果）

***通过做一个简单的案例陈述，如下所示，翻译发生得很好

用于测试临时表中 o​​rd_status 数据的用例转换的基本查询。输出正确

select ord_status,
 CASE
     WHEN RTRIM([ord_status]) = 'N' THEN 'New'
     WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
     WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
     ELSE ''
 END
 from #tempShip

***正在更新临时表的存储过程结束

UPDATE  [#tempShip] SET
        [#tempShip].[DUP] = 1,
        [#tempShip].[ord_status] = 
           CASE
                 WHEN RTRIM([ord_status]) = 'N' THEN 'New'
                 WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
                 WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
                 ELSE ''
           END

        FROM #tempShip
         INNER JOIN
    (
        SELECT X.order_N_ext, COUNT(X.order_N_ext) AS dup
        FROM #tempShip AS X
        GROUP BY X.order_N_ext
    ) AS Y
         ON #tempShip.order_N_ext = Y.order_N_ext
    WHERE Y.dup > 1;
    ----- -- mbs 3/30/2017  #22844 - add parameter @ship_type 
    SELECT * FROM #tempShip 
    WHERE ship_type LIKE @ship_type 
    -------<<<


END
DROP TABLE #tempShip

**order_N_ext

SELECT O.order_no
    , O.ext
    , CONVERT(varchar(20), O.order_no) + ' - ' + CONVERT(varchar(2), O.ext) 
AS 'order_N_ext'

数据示例：

Ord_Num  BD order_N_ext Loc     Cust        Ship_date                      Ord_Status
1252868  1  1252868 - 1 RAS HQ  21115   2017-04-19 00:00:00.000 2570.63    P       
1253995  1  1253995 - 1 RAS HQ  11471   2017-04-19 00:00:00.000 1270.45    Q       
1254526  0  1254526 - 0 RAS HQ  24104   2017-04-19 00:00:00.000 2214.67    P             

有没有人有引用系统信息的有效方法来搜索 SQL Server 2000 实例中所有数据库的存储过程？我目前的任务是将水晶报表迁移到 SSRS，客户有大约 500 个或更多的水晶报表，其存储过程分布在多个数据库中。我知道我可以按文本过滤每个数据库的可编程性文件夹，但我正在寻找一个结果，我可以在其中查询 master.dbo.sysdatabases 并将其引用到 syscomments，我可以在其中指定文本并找到与数据库关联的存储过程。