drop table t;
create table t(c number);
insert into t values(1);
insert into t values(2);
insert into t values(3);
insert into t values(5);
insert into t values(7);
insert into t values(8);
insert into t values(9);
insert into t values(11);
insert into t values(12);
insert into t values(15);
insert into t values(16);
insert into t values(20);
commit;
select min(c) start_range,max(c) end_range
from t
group by c-rownum
order by 1;
create table t(START_RANGE number, end_range number);
Insert into t (START_RANGE,END_RANGE) values (1,3);
Insert into T (START_RANGE,END_RANGE) values (5,5);
Insert into T (START_RANGE,END_RANGE) values (7,9);
Insert into T (START_RANGE,END_RANGE) values (11,12);
Insert into T (START_RANGE,END_RANGE) values (15,16);
Insert into T (START_RANGE,END_RANGE) values (20,20);
SELECT start_range,
end_range,
r-1 seq,
start_range+r-1 result
FROM t,
lateral (select rownum r
from dual
connect by level <= (end_range-start_range)+1);
select start_range,end_range,r
from t,
(select rownum r
from
dual connect by level <=(
select max(end_range)
from t)) d
where start_range <= r and end_range >=r;
Below Answers Received from subscribers.... Thanks for answering :-)
SELECT DISTINCT (LEVEL + START_RANGE)-1 NUM
FROM T
CONNECT BY (LEVEL +START_RANGE ) <= END_RANGE+1
ORDER BY NUM;
---------------------------------------------------------
create table cars (
name varchar2(100),
start_year number,
end_year number);
insert into cars values('Swift',2007,2009);
insert into cars values('Swift',2009,2011);
insert into cars values('Swift',2011,2013);
insert into cars values('Maruti',2009,2011);
insert into cars values('Maruti',2013,2015);
commit;
with r as (select distinct name,start_year+r year
from cars,
lateral(select rownum-1 r
from dual connect by level <=end_year-start_year+1)
order by 1,2)
select name,min(year) s_year,max(year) e_year
from r
group by name,year-rownum;
Sir my self ujjwal I'm from Lucknow ...I want to learn ETL(Informatica)...pls call me 8400646059.... Mail id : ujjwal18@gmail.com
ReplyDeleteanother way of writing query
ReplyDeleteselect name, min(yr), max(yr) from (
select name, least( start_year, end_year) yr from
cars
union all
select name, greatest( start_year, end_year) from
cars)
group by name
output of this query is not same as expected output.
DeleteHi Siva
ReplyDeleteTo print sequence number we can use the below query.
select min(c) start_range
from a
group by c+rownum;
Here is my approach for last problem
ReplyDeletewith ans as (select r,name from cars,(select rownum-1+(select min(start_year) from cars) r from dual
connect by level <=(select max(end_year)-min(start_year)+1 from cars))
where r between start_year and end_year order by name,r)
select name,min(r) st_yr,max(r) ed_yr from (select r,name,r-rownum al from (select distinct r,name from ans order by name,r)) group by al,name order by name desc,st_yr