CODEWITHSUNDEEP
sqlteradata
cs0815
cs0815

Reputation: 17388

minimum of several columns ignoring zero and null

In TeraData, is there a simple way to select the minimum of several columns (i.e. creating a computed column) whilst ignoring columns containing zeros and NULLs?

Upvotes: 0

Views: 891

Answers (2)

dnoeth
dnoeth

Reputation: 60462

There's a LEAST function, but it doesn't ignore NULLs, thus you have to apply some additional logic like

LEAST(COALESCE(NULLIF(col1,0), 999999)
     ,COALESCE(NULLIF(col2,0), 999999)
     ,COALESCE(NULLIF(col3,0), 999999))

For 99999 you should use a value higher than any existing. If all columns might be NULL or zero you must add a final

NULLIF(LEAST(....), 99999)

to return NULL in this case.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Well, you can sort of use least():

select least(col1, col2, col3)

This is tricky with 0s and NULLs. You don't specify the types of columns, but you can use NULLIF() and COALESCE() for positive numeric values:

select least( coalesce(nullif(col1, 0), 999999999),
              coalesce(nullif(col2, 0), 999999999),
              coalesce(nullif(col2, 0), 999999999)
            )

Of course, this returns the special value 999999999 if all values are 0 or NULL.

Upvotes: 1

Related Questions