# Converting a Crystal Report Formula to a CASE expression

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',
``````

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'
``````