Sample Header Ad - 728x90

Converting a Crystal Report Formula to a CASE expression

1 vote
1 answer
1869 views
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',
Asked by tfenwick11 (171 rep)
May 18, 2017, 12:59 PM
Last activity: May 18, 2017, 01:24 PM