CODEWITHSUNDEEP
sqlpostgresqlgreenplum
user8545255
user8545255

Reputation: 849

If statement inside select clause (postgresql)

I want to set 0 or 1 flag based on column value.if the value is > 0.0 AND <= 99.0 then 1 ,else 0 .

              Score         flag_score

              0.083642      1
              0.009542      1
              0.999999      1
              101.0000      0

Upvotes: 1

Views: 183

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I would write this simply as:

select t.*, ( (value > 0.0) and (value <= 99.0) )::int as flag_score
from t;

Postgres has a nice shorthand for declaring flags, so case expressions are not needed.

If you want to actually set the value, you can do:

update t
    set flag = ( (value > 0.0) and (value <= 99.0) )::int;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Have you tried case expression ?

select *, 
        (case when value > 0.0 and value <= 99.0 then 1 else 0 end) flag_score
from table;

Upvotes: 3

Related Questions