CODEWITHSUNDEEP
sqldatabasehana
PassioEtDesiderium
PassioEtDesiderium

Reputation: 23

How to replace NULL values with the following NOT NULL value? (SQL)

How can I replace NULL values with the following NOT NULL value (so the next row with a value in that column that is NOT NULL)?

Imagine following table (click on it):

TABLE

a    100
b    NULL
c    102
d    NULL
e    NULL
f    104

Goal is that a is 100, b, c are 102 and d, e, f are 104.

I use HANA as database.

Best regards. Thank you.

Upvotes: 2

Views: 2206

Answers (1)

Tom H
Tom H

Reputation: 47464

I'm not familiar with HANA, but this SQL is pretty generic and ANSI compliant, so it should work with most SQL engines. Some of the simpler engines don't support subqueries, but most should.

SELECT
    T1.col1,
    COALESCE(T1.col2, T2.col2) AS col2
FROM
    My_Table T1
LEFT OUTER JOIN My_Table T2 ON
    T2.col1 =
        (
        SELECT MIN(T3.col1)
        FROM My_Table T3
        WHERE
            T3.col1 > T1.col1 AND
            T3.col2 IS NOT NULL
        )

The idea of the query is that it JOINs the table to itself to get the next row (the T2 table alias) without a NULL value in col2.

Upvotes: 1

Related Questions