1

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

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.