Search code examples
sqlmathnullimpala

Replacing NULL values Impala query

I'm trying to create a SQL statement in Imapala that would replace NULL values with 1. The fields generated out of this output will then be multiplied by each other.

I thought coalesce would work but I'm still NULLS in the output. Below is my syntax, do I have the 1 in the wrong place. How would I write this? Please help.

SUM(
    COALSECE(
        CASE WHEN a.level = '8' AND a.assembly_flag = 'X' THEN
            COALSECE( a.inclusion_kg, 1 )
        END
    )
) level_8_inclusion

Tried to use coalesce in different places in the syntax.

Solution

  • As mentioned in comments by @Dai, COALESCE requires 2 arguments :

    This will return 1 when if the sum is NULL

    COALESCE(
    SUM(
        CASE WHEN a.level = '8' AND a.assembly_flag = 'X' THEN
            a.inclusion_kg
        END
    ), 1 -- second argument
) level_8_inclusion