Sql-Server-2000
將 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'