Converting a Crystal Report Formula to a CASE expression

Question

With attempting to convert a crystal report formula into a SQL CASE expression, I seem to be having trouble understanding the concept of "total_pallet_weight" and "order_no" utilizing the "AND" in the formula with "cubic feet" and "order_no". I thought with my attempt below I would start out with a CASE just for "total_pallet_weight" then have a sub CASE for cubic feet to represent the "AND" in the formula. But I'm not to familiar with how the syntax "({a_ras_shipping_order_PCF_vw.total_pallet_weight}, {a_ras_truck_shipment_vw.order_no}) > 0" works. Is the forumla saying "total pallet weight + order_no > 0"?

Crystal Report Formula:

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

Attempt at replacing formula:

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'

Syntax for "total pallet weight"

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

Syntax for "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',

Answer 1

It looks as though "order_no" is not used a reference for the CASE expression unlike the formula. With making some minor tweaks and replacing my "+" in my sub CASE with "/" I received the correct outcome.

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'