Decode in View

[mlm_montana]

I am trying to build a view of a table which includes a field for the year of data collection. In the view I want a column that contains "actual" if the year is 2009, and "estimated if the year is before 2009. I can use decode for each year on the table, but is there a way to use "< 2009" instead?

 

[patch]

You could use the case statement:

drop table t purge;
create table t (year number);
insert into t (year) values (2006);
insert into t (year) values (2007);
insert into t (year) values (2008);
insert into t (year) values (2009);
insert into t (year) values (2010);
commit;
create or replace view v as
(select year
, case
when year < 2009 then 'estimated'
else 'actual'
end is_actual
from t);
select * from v;

Kindest regards,

 

[jlcox]

You can use the sign() function. sign(year - 2009) will return 1 if year is > 2009, 0 if = 2009, -1 if < 2009

decode(sign(year - 2009), -1, 'Actual', 'Estimated') is_actual

 

[mlm_montana]

Thanks both of you!

I tried Patrick's solution, and it works great!

Marisa

 

[Jeff_1947]

Just as an aside. Case is more efficient since it is part of the sql engine while decode is a function that requires a context switch. Also case is much easier to read especially if you have decode with a decode within a ....

 

[vllr]

I don't think any of build-in Oracle functions require context switch. This is true for custom functions. But I agree that Case is much easier to read.