SQL> select MIN(c_year), MAX(c_year)
2 from T
3 ;

MIN(C_YEAR) MAX(C_YEAR)
--------------------------- -----------------------------------------------
-
1996 2005

There may be gaps in this column value...
How can I ensure to return a result of CONSECUTIVE values...like:

1996
1997
1998
1999
2000
2001
2002
2003
2004
2005

Thanks

"Robert C" <rchin@panix.com> wrote in message
news:bpdvhc$2fu$1@reader2.panix.com...
> SQL> select MIN(c_year), MAX(c_year)
> 2 from T
> 3 ;
>
> MIN(C_YEAR) MAX(C_YEAR)
> --------------------------- ---------------------------------------------
--
> -
> 1996 2005
>
> There may be gaps in this column value...
> How can I ensure to return a result of CONSECUTIVE values...like:
>
> 1996
> 1997
> 1998
> 1999
> 2000
> 2001
> 2002
> 2003
> 2004
> 2005
>
> Thanks
>
>

1 select 1995 + rn
2 from (select rownum rn from dba_objects)
3* where rn between 1 and 10
sql>/

1995+RN
----------
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005

robert,

your post is a little unclear (or maybe my reading of it is)... if there are
gaps in the column values, the literal answer to your questions is you can't
ensure returning a set of consecutive values, because by definition you
don't have one

however, if you're asking how to determine if there are gaps in the sequence
then you can do something like this

select t1.c_year
from t t1, t t2
where t1.c_year + 1 = t2.c_year(+)
and t2.c_year is null
and t1.c_year <> (select max(c_year) from t)

this is basically an outer self join that tries to match each record with
one for the following year -- outer join includes years that do not have a
matching 'next' year, and the IS NULL predicate filters the output to just
those years
the subquery eliminates the max year, as it will never have a 'next' year

this will return a list of years that precede a gap, but won't identify how
big the gap is

--mcs


"Robert C" <rchin@panix.com> wrote in message
news:bpdvhc$2fu$1@reader2.panix.com...
| SQL> select MIN(c_year), MAX(c_year)
| 2 from T
| 3 ;
|
| MIN(C_YEAR) MAX(C_YEAR)
| --------------------------- ---------------------------------------------
--
| -
| 1996 2005
|
| There may be gaps in this column value...
| How can I ensure to return a result of CONSECUTIVE values...like:
|
| 1996
| 1997
| 1998
| 1999
| 2000
| 2001
| 2002
| 2003
| 2004
| 2005
|
| Thanks
|
|

On Tue, 18 Nov 2003 15:30:36 -0500, "Robert C" <rchin@panix.com>
wrote:

>There may be gaps in this column value...
>How can I ensure to return a result of CONSECUTIVE values...like:

I've written about this problem. Have a look at the following two
articles:

http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html

http://four.pairlist.net/pipermail/o...03/000006.html

The first article should help. The second talks about a new 10g
feature that bears on this problem.

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com

Join the Oracle-article list and receive one article on Oracle
technologies per month by email. To join, visit
http://four.pairlist.net/mailman/lis...oracle-article, or send
email to Oracle-article-request@gennick.com and include the word
"subscribe" in either the subject or body.

"Robert C" <rchin@panix.com> wrote

> SQL> select MIN(c_year), MAX(c_year)
> 2 from T
> 3 ;
>
> MIN(C_YEAR) MAX(C_YEAR)
> --------------------------- -----------------------------------------------
> -
> 1996 2005
>
> There may be gaps in this column value...
> How can I ensure to return a result of CONSECUTIVE values...like:
>
> 1996
> 1997
> 1998
> 1999
> 2000
> 2001
> 2002
> 2003
> 2004
> 2005

You create a lookup dimension/table. You populate it with the serie
you want. You then join from it to the applicable table using an outer
join.

In Data Warehousing you will often find a date dimension/table that
contains a row per day for years X to Z. Or a time dimension that
contains a row per second for a 24h period.

--
Billy