將 Crystal Report 公式轉換為 CASE 表達式

在嘗試將水晶報表公式轉換為 SQL CASE 表達式時，我似乎無法理解“total_pallet_weight”和“order_no”的概念，在“立方英尺”和“order_no”的公式中使用“AND”。我想通過下面的嘗試，我將從一個僅用於“total_pallet_weight”的 CASE 開始，然後有一個用於立方英尺的子 CASE 來表示公式中的“AND”。但我不熟悉語法“（{a_ras_shipping_order_PCF_vw.total_pallet_weight}，{a_ras_truck_shipment_vw.order_no}）> 0”的工作原理。論壇是否說“托盤總重量 + 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',

與公式不同，看起來好像“order_no”沒有用作 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'

引用自：https://dba.stackexchange.com/questions/173935