Solved

Pl/SQL or SQL

Posted on 2014-03-21
10
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
10 Comments
 
LVL 32

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 32

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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
 

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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
The purpose of this article is to demonstrate how we can use conditional statements using Python.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

739 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