Exclude row based on multiple columns

Question

I'm trying to exclude rows where all gross columns have blank values.

Sample data:

market   item_id    gross_1     gross_2   gross_3   period
POLAND   1111       1           2         3         20190301
ESTONIA  2222       blank       2         3         20190302
POLAND   3333       1           blank     3         20190303
POLAND   3333       1           blank     blank     20190304
POLAND   4444       blank       blank     blank     20190305
POLAND   5555       1           2         3         20190306`

I'd like to receive:

market   item_id    gross_1     gross_2   gross_3   period
POLAND   1111       1           2         3         20190301
ESTONIA  2222       blank       2         3         20190302
POLAND   3333       1           blank     3         20190303
POLAND   3333       1           blank     blank     20190304
POLAND   5555       1           2         3         20190306`

I've found something like this, but it doesn't work in snowflake:

WHERE NOT ROW(gross_1, gross_2, gross_3) IS NULL

What does "blank" mean?

Gordon Linoff
– Gordon Linoff

2019-09-20 12:48:48 +00:00
Commented Sep 20, 2019 at 12:48 — Gordon Linoff
– Gordon Linoff, Commented Sep 20, 2019 at 12:48

forpas · Accepted Answer · 2019-09-20 12:50:50Z

5

Use coalesce():

select * from tablename
where coalesce(gross_1, gross_2, gross_3) is not null

answered Sep 20, 2019 at 12:50

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Brandt Over a year ago

Elegant, if he means NULL. +1 Wrapping each element in a NULLIF for an empty string or the string 'blank' would work with the COALESCE, too. Nice one.

forpas Over a year ago

All 3 columns contain integers so I guess it's about nulls.

Gordon Linoff · Accepted Answer · 2019-09-20 12:53:03Z

2

It depends what "blank" means. As shown in the data:

select t.*
from t
where not (gross_1 = 'blank' and gross_2 = 'blank' and gross_3  = 'blank')

If "blank" means empty string, then:

select t.*
from t
where not (gross_1 = '' and gross_2 = '' and gross_3 = '')

If it means NULL, then:

select t.*
from t
where not (gross_1 is null and gross_2 is null and gross_3 is null)

All these can be rephrased with or:

select t.*
from t
where gross_1 is not null or gross_2 is not null or gross_3 is not null

edited Sep 20, 2019 at 12:53

answered Sep 20, 2019 at 12:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

The Impaler Over a year ago

He wants to exclude them rather than include them, but yeah, this is how it's done.

Gordon Linoff Over a year ago

@dvo . . . I left out the not.

Collectives™ on Stack Overflow

Exclude row based on multiple columns

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related