IFNULL around a complicated Case Function select column

Question

How do I fix my IFNULL so that it doesn't break the below? If I remove the IFNULL than it works fine, but I need to calculate data off that column and need to get rid of the null values and replace with a 0.

 IFNULL(CASE 
    WHEN A.Industry = 'Transportation & Warehousing' THEN 26
    WHEN A.Industry = 'Construction' THEN 19
    WHEN A.Industry = 'Field Services' THEN 26
    WHEN A.Industry = 'Wholesale Trade' THEN 26
    WHEN A.Industry = 'Manufacturing' THEN 30
    WHEN A.Industry = 'Consumer Products' THEN 26
    WHEN A.Industry = 'Retail Trade' THEN 26
    WHEN A.Industry = 'Passenger Transit' THEN 25
    WHEN A.Industry = 'Mining, Quarrying, Oil & Gas' THEN 25
    WHEN A.Industry = 'Food & Beverage' THEN 26
    WHEN A.Industry = 'Utilities' THEN 26
    WHEN A.Industry = 'Health Care & Social Assistance' THEN 25
    WHEN A.Industry = 'Government' THEN 26
    WHEN A.Industry = 'Educational Services' THEN 25
    ELSE 254
    END * Number_Of_Vehicles *0.99) + 
(CASE 
    WHEN A.Industry = 'Transportation & Warehousing' THEN 34
    WHEN A.Industry = 'Construction' THEN 32
    WHEN A.Industry = 'Field Services' THEN 33
    WHEN A.Industry = 'Wholesale Trade' THEN 36
    WHEN A.Industry = 'Manufacturing' THEN 39
    WHEN A.Industry = 'Consumer Products' THEN 42
    WHEN A.Industry = 'Retail Trade' THEN 31
    WHEN A.Industry = 'Passenger Transit' THEN 32
    WHEN A.Industry = 'Mining, Quarrying, Oil & Gas' THEN 32
    WHEN A.Industry = 'Food & Beverage' THEN 23
    WHEN A.Industry = 'Utilities' THEN 32
    WHEN A.Industry = 'Health Care & Social Assistance' THEN 32
    WHEN A.Industry = 'Government' THEN 42
    WHEN A.Industry = 'Educational Services' THEN 39
    ELSE 32
    END * Number_Of_Vehicles * 0.49),0)

Tried to add an ifnull, but unsure how to do that in this case with the correct format.

Answer 1

The immediate issue is with parentheses, that are not well balanced. You don't need parentheses around the CASEs, only one pair is needed, for the IFNULL() function.

Also, you could simplify the CASE expressions so that the checked column is not repeated again and again. I also find that it would be easier to follow if there was a single CASE expressions with WHEN branches managing the whole arithmetic, rather than two distinct expressions with similar branches.

We could phrase this as:

IFNULL(
    CASE A.Industry 
        WHEN 'Transportation & Warehousing'     THEN 26 * Number_Of_Vehicles * 0.99 + 34 * Number_Of_Vehicles * 0.49
        WHEN 'Construction'                     THEN 19 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Field Services'                   THEN 26 * Number_Of_Vehicles * 0.99 + 33 * Number_Of_Vehicles * 0.49
        WHEN 'Wholesale Trade'                  THEN 26 * Number_Of_Vehicles * 0.99 + 36 * Number_Of_Vehicles * 0.49
        WHEN 'Manufacturing'                    THEN 30 * Number_Of_Vehicles * 0.99 + 39 * Number_Of_Vehicles * 0.49
        WHEN 'Consumer Products'                THEN 26 * Number_Of_Vehicles * 0.99 + 42 * Number_Of_Vehicles * 0.49
        WHEN 'Retail Trade'                     THEN 26 * Number_Of_Vehicles * 0.99 + 31 * Number_Of_Vehicles * 0.49
        WHEN 'Passenger Transit'                THEN 25 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Mining, Quarrying, Oil & Gas'     THEN 25 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Food & Beverage'                  THEN 26 * Number_Of_Vehicles * 0.99 + 23 * Number_Of_Vehicles * 0.49
        WHEN 'Utilities'                        THEN 26 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Health Care & Social Assistance'  THEN 25 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
        WHEN 'Government'                       THEN 26 * Number_Of_Vehicles * 0.99 + 42 * Number_Of_Vehicles * 0.49
        WHEN 'Educational Services'             THEN 25 * Number_Of_Vehicles * 0.99 + 39 * Number_Of_Vehicles * 0.49
        ELSE                                        254 * Number_Of_Vehicles * 0.99 + 32 * Number_Of_Vehicles * 0.49
    END,
    0
)