Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Summarise the results based on start and end dates

cdprasadJul 15 2010 — edited Jul 15 2010
Hi All,

Can some body tells how to summarise a table results like in the below example?
I need help to get results as poc_dates_result from poc_dates table.
The data is supplied below to create the above tables.
Please provide the queries to convert poc_dates to poc_dates_result?

DROP TABLE poc_dates;

create table poc_dates
(
pid number
,start_dte date
,end_dte date
,prod char(3)
);

insert into poc_dates values(1,'01-JAN-2000','01-FEB-2000','PD1');
insert into poc_dates values(1,'02-FEB-2000','01-MAR-2000','PD1');
insert into poc_dates values(1,'02-MAR-2000','01-APR-2000','PD1');

insert into poc_dates values(1,'02-APR-2000','15-APR-2000','PD2');
insert into poc_dates values(1,'16-APR-2000','24-APR-2000','PD2');

insert into poc_dates values(1,'25-APR-2000','01-MAY-2000','PD3');
insert into poc_dates values(1,'02-MAY-2000','16-MAY-2000','PD3');

insert into poc_dates values(1,'17-MAY-2000','18-MAY-2000','PD1');
insert into poc_dates values(1,'19-MAY-2000','25-MAY-2000','PD1');

insert into poc_dates values(2,'01-JAN-2000','01-FEB-2000','PD1');
insert into poc_dates values(2,'02-FEB-2000','01-MAR-2000','PD1');

insert into poc_dates values(2,'02-MAR-2000','01-APR-2000','PD2');

insert into poc_dates values(2,'02-APR-2000','15-APR-2000','PD1');

insert into poc_dates values(2,'16-APR-2000','25-APR-2000','PD3');

insert into poc_dates values(3,'01-JAN-2000','01-FEB-2000','PD1');
insert into poc_dates values(3,'02-FEB-2000','01-MAR-2000','PD1');

insert into poc_dates values(3,'02-MAR-2000','01-APR-2000','PD2');

insert into poc_dates values(4,'01-JAN-2000','01-FEB-2000','PD1');

insert into poc_dates values(4,'02-FEB-2000','01-MAR-2000','PD2');

insert into poc_dates values(5,'01-JAN-2000','01-FEB-2000','PD1');

commit;

drop table poc_dates_result;

create table poc_dates_result
(
pid number
,start_dte date
,end_dte date
,prod char(3)
);

insert into poc_dates_result values(1,'01-JAN-2000','01-APR-2000','PD1');
insert into poc_dates_result values(1,'02-APR-2000','24-APR-2000','PD2');
insert into poc_dates_result values(1,'25-APR-2000','16-MAY-2000','PD3');
insert into poc_dates_result values(1,'17-MAY-2000','25-MAY-2000','PD1');
insert into poc_dates_result values(2,'01-JAN-2000','01-MAR-2000','PD1');
insert into poc_dates_result values(2,'02-MAR-2000','01-APR-2000','PD2');
insert into poc_dates_result values(2,'02-APR-2000','15-APR-2000','PD1');
insert into poc_dates_result values(2,'16-APR-2000','25-APR-2000','PD3');
insert into poc_dates_result values(3,'01-JAN-2000','01-MAR-2000','PD1');
insert into poc_dates_result values(3,'02-MAR-2000','01-APR-2000','PD2');
insert into poc_dates_result values(4,'01-JAN-2000','01-FEB-2000','PD1');
insert into poc_dates_result values(4,'02-FEB-2000','01-MAR-2000','PD2');
insert into poc_dates_result values(5,'01-JAN-2000','01-FEB-2000','PD1');

commit;

select * from poc_dates;

PID START_DTE END_DTE PROD
1 1/01/2000 1/02/2000 PD1
1 2/02/2000 1/03/2000 PD1
1 2/03/2000 1/04/2000 PD1
1 2/04/2000 15/04/2000 PD2
1 16/04/2000 24/04/2000 PD2
1 25/04/2000 1/05/2000 PD3
1 2/05/2000 16/05/2000 PD3
1 17/05/2000 18/05/2000 PD1
1 19/05/2000 25/05/2000 PD1
2 1/01/2000 1/02/2000 PD1
2 2/02/2000 1/03/2000 PD1
2 2/03/2000 1/04/2000 PD2
2 2/04/2000 15/04/2000 PD1
2 16/04/2000 25/04/2000 PD3
3 1/01/2000 1/02/2000 PD1
3 2/02/2000 1/03/2000 PD1
3 2/03/2000 1/04/2000 PD2
4 1/01/2000 1/02/2000 PD1
4 2/02/2000 1/03/2000 PD2
5 1/01/2000 1/02/2000 PD1


select * from poc_dates_result;

PID START_DTE END_DTE PROD
1 1/01/2000 1/04/2000 PD1
1 2/04/2000 24/04/2000 PD2
1 25/04/2000 16/05/2000 PD3
1 17/05/2000 25/05/2000 PD1
2 1/01/2000 1/03/2000 PD1
2 2/03/2000 1/04/2000 PD2
2 2/04/2000 15/04/2000 PD1
2 16/04/2000 25/04/2000 PD3
3 1/01/2000 1/03/2000 PD1
3 2/03/2000 1/04/2000 PD2
4 1/01/2000 1/02/2000 PD1
4 2/02/2000 1/03/2000 PD2
5 1/01/2000 1/02/2000 PD1


Thanks,
CD

Comments

Sdhamoth-Oracle

Hi,

Did you set TNS_ADMIN environment variable? If not, can you try after setting it?

Thanks,

Senthil

TheVenkster

Senthil:

What should it point to?

Anyway, I re-installed both the 64-bit Oracle DB and the 32-bit client. There are two sets of TNSNames.Ora, Listener.Ora and SQLNet.ora, one for the 64-bit and one for the 32-bit in their respective folders.

I have no problem creating an ODBC DSN for the 64-bit version. The link connects fine to the DB

I have two TNS Listeners defined, one for the 64-bit and one for the 32-bit. While the 64-bit uses Port 1521, I am using Port 1522 for the 32-bit; I also made sure that Listener.ora refers to the same port. But when I use Oracle Net Configuration Assistant to configure Local Net Service Name Configuration to test the Service, it says "TNS:listener does not currently know of the service requested in connect descriptor.

I did check that both the TNS Listener services are running.

venki

TheVenkster

Senthil:

I don't know what happened but now, everything works just as expected. I am able to attach Oracle DB tables to an Access database using ODBC. I am able to also connect to the DB using the 64-bit drivers.

Venki

1 person found this helpful
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 12 2010
Added on Jul 15 2010
8 comments
1,200 views