select 'range' - help

Hi,
I need help with populating the column range3 as described below.

Code:

---

create table test (id number, range1 varchar2(20), range2 varchar2(20), range3 varchar2(20));
insert into test values (1, 2000-2002,1999-2001,'');
insert into test values (2, 1998,1999-2001,'');
insert into test values (3, 1998-2001,2002,'');
insert into test values (4, '',2002-2003,'');
insert into test values (5, 2000-2001,'','');
commit;

---

range3 is a SUPERSET of columns range1 and range2.

In other words :
for id=1, range3 ought to be = 2000-2002
for id=2, range3 ought to be = 1998-2001
for id=3, range3 ought to be = 1998-2002
for id=4, range3 ought to be = 2002-2003
for id=5, range3 ought to be = 2000-2001

What's a good way to accomplish this?

Thanks, Maddy

Is there some business rule here that says that there will only be one row per id in the final result set? Is there also a reason why you don't have this broken out into a table of ID and YEAR such as ...

Code:

---

1, 1999
1, 2000
1, 2001
1, 2002
...

---

or

Code:

---

1, 2000, 2002,
1, 1999, 2001

---

By the way your test script has an error ... unless you surround 2000-2002 in single quotes it will evaluate it as a formula, giving the result -2, befiore inserting the value into test.

Oh, here you go anyway ...

Code:

---

drop table test;
create table test
  (id number,
    range1 varchar2(20),
    range2 varchar2(20),
    range3 varchar2(20));
   
insert into test values (1, '2000-2002','1999-2001','');
insert into test values (2, '1998','1999-2001','');
insert into test values (3, '1998-2001','2002','');
insert into test values (4, '','2002-2003','');
insert into test values (5, '2000-2001','','');
commit;

select id,
least(substr(Nvl(range1,'x'),1,4),substr(Nvl(range2,'x'),1,4),substr(Nvl(range3,'x'),1,4))||'-'||
greatest(substr(Nvl(range1,'0000'),-4,4),substr(Nvl(range2,'0000'),-4,4),substr(Nvl(range3,'0000'),-4,4))
 from test;

        ID LEAST(SUB
---------- ---------
        1 1999-2002
        2 1998-2001
        3 1998-2002
        4 2002-2003
        5 2000-2001

---

you just couldn't resist, could you?

Quote:

---

Originally Posted by marist89

you just couldn't resist, could you?

---

I'm a sucker for anything with a substr.

Quote:

---

Originally Posted by slimdave

Oh, here you go anyway ...

---

Slimdave, Thank you so much! That was incredibly helpful! I'm a statistician and rarely write sql..am not very good at it. :)

As for your questions :

> Is there some business rule here that says that there will only be one row per id in the final result set?

Yes.

> Is there also a reason why you don't have this broken out into a table of ID and YEAR such as ...

Thats the way the data appears in the table.


Good catch on the quotes!

Slimdave,
We have the same tables residing on both Oracle and MS Sqlserver databases. While your solution worked quite nicely on our Oracle tables, it didn't work on our sqlserver tables. :(

Do you by any chance know the equivalent of the "least" and "greatest" functions in Sqlserver? Is there one?

I realize that this is an Oracle forum..but haven't had much luck googling for this information.

Thank you.
Maddy