Solved

Pl/SQL or SQL

Posted on 2014-03-21
10
186 Views
Last Modified: 2014-03-23
Hi,

Attached you can find an xls file with my problem and necessary explanations.
I tried to build an SQL but without success and my knowledge of pl/sql are minimum.
Can anyone help me?
Sorry for my poor english.

Thank you
PL.SQL.xls
0
Comment
Question by:marian68
  • 6
  • 2
  • 2
10 Comments
 
LVL 31

Expert Comment

by:awking00
ID: 39945640
This is a little difficult to visualize. May I assume that your desired result of TT5 - T3 on line 11 is because TT4 is less than T3 but TT5 is greater than T3? Also, your desired results show one row fewer than the rows in the first table and one of your requirements is that they be the same number of records. Can you possibly provide sample data with actual dates and times plus your expected output so we have something to test with? It would also help clarify your intent.
0
 

Author Comment

by:marian68
ID: 39945754
Sorry for the mistakes and for not being able to sent the original data.
I corrected the mistakes and I put the dates in choronological order for the first 2 products.
But the easiest way to think about my problem is:
I sent 5 letters and received 10 letters. I would like to know the time frame between the date of each letter I sent and the date of the 1st letter received after I sent the corresponding letter.
So I will have 5 dates for the sent letters and 5 dates for the received letters.

Thank you
PL.SQL.xls
0
 
LVL 31

Expert Comment

by:awking00
ID: 39946218
You don't need to send original data but it would really be better if we had real dates and times to work with. In ascii, TT10 is less than TT9, so real date comparisons will be required.
0
 

Author Comment

by:marian68
ID: 39946411
Ok. I attached an particular example.
I would very satisfied for beginning to have a solution for this example.

Thank you
PL.SQL.xls
0
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39946531
I think sql is enough:
example

with send
as
(
select 'product1' product, to_date('29-5-2007 ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('28-8-2007 ','dd-mm-yyyy') ldate from dual union
select 'product3' product, to_date('15-2-2008 ','dd-mm-yyyy') ldate from dual
)
, recieved
as
(
select 'product1' product, to_date('5-1-2007  ','dd-mm-yyyy') ldate from dual union
select 'product1' product, to_date('8-8-2007  ','dd-mm-yyyy') ldate from dual union
select 'product1' product, to_date('5-1-2010  ','dd-mm-yyyy') ldate from dual union
select 'product1' product, to_date('5-7-2010  ','dd-mm-yyyy') ldate from dual union
select 'product1' product, to_date('5-1-2011  ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('30-1-2007 ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('17-5-2007 ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('28-12-2007','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('14-5-2008 ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('14-11-2008','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('14-5-2009 ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('12-11-2009','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('12-5-2010 ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('30-6-2010 ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('31-3-2011 ','dd-mm-yyyy') ldate from dual union
select 'product2' product, to_date('31-3-2011 ','dd-mm-yyyy') ldate from dual union
select 'product3' product, to_date('15-8-2007 ','dd-mm-yyyy') ldate from dual union
select 'product3' product, to_date('16-6-2008 ','dd-mm-yyyy') ldate from dual union
select 'product3' product, to_date('14-10-2008','dd-mm-yyyy') ldate from dual union
select 'product3' product, to_date('4-3-2009'  ,'dd-mm-yyyy') ldate from dual union
select 'product3' product, to_date('24-8-2009' ,'dd-mm-yyyy') ldate from dual
)
select product, sent, recieved, recieved - sent difference_days
from
(
select send.product, send.ldate sent, min(recieved.ldate) recieved
from send, recieved
where recieved.product = send.product
and   recieved.ldate > send.ldate
group by send.product, send.ldate
)
order by product, sent
/

The date difference does not match the example, but probably will if the  correct times are available
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:marian68
ID: 39948928
Hi,

Thank you for your answer and sorry for my delayed answer.
Have you tested it ?
First your SQL doesn't recongnize.recieved.ldate or send.ldate.
After I solved this problem I receive the message after 15 minutes;
"ora-01652 enable to extend segment by 128 in tablespace TEMP"
0
 

Author Comment

by:marian68
ID: 39948953
I checked also only for the 3 products I sent you.
It doesn't work.
0
 
LVL 20

Expert Comment

by:flow01
ID: 39949133
Yes I did test it against ORACLE XE 11g version.
Since I dont't know your tablenames I created fictive tables recieved and send with fictive columnnames by using the with CLAUSE.  But you solved that.
Did you limit to the 3 products by adding an and condition in the inner query or where condition in the outer query.
Are you familiar with explain plan ?
How many rows are there in both tables ?  
Is there an index on the product column of the second table.
Is there room in the tablespace TEMP before you execute the query ?
0
 

Author Comment

by:marian68
ID: 39949203
Hi,

Thank you for your answer.
I tested your SQL and it worked.
When I use tables in a SQL built exactly as in your answer it doesn't work any more.
Concerning the number of records we speak about millions.
Yes I have indexes on the product in the both tables.
No I am not familiar with explain plan or tablespace temp.
Anyway because your SQL works for my example I will give you the points.
I have asked another question 3 hours ago very similar with this, only that for each product I can have more than 1 sent date - you will see in the my attached xls file.
Please for the second question try to put my data in 2 tables and test the SQL using these tables. We have to find why my SQL didn't work.
Thanks a lot,
0
 

Author Closing Comment

by:marian68
ID: 39949204
Thank you
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The viewer will learn how to implement Singleton Design Pattern in Java.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now