select 'range' - help

[maddy33]

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

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

[slimdave]

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 ...

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

or

1, 2000, 2002,
1, 1999, 2001

[slimdave]

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.

[slimdave]

Oh, here you go anyway ...

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

[marist89]

you just couldn't resist, could you?

[slimdave]

you just couldn't resist, could you?

I'm a sucker for anything with a substr.

[maddy33]

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!

[maddy33]

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