主页 / dba / 问题 / 173935
Accepted
tfenwick11
Asked: 2017-05-19 04:59:37 +0800 CST

将 Crystal Report 公式转换为 CASE 表达式

  • 772

在尝试将水晶报表公式转换为 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-2000 case

1 个回答

  1. Best Answer

    与公式不同，看起来“order_no”并未用作 CASE 表达式的引用。通过进行一些小的调整并将子 CASE 中的“+”替换为“/”，我收到了正确的结果。

    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'
    • 0

相关问题