Solved

Pl/SQL or SQL

Posted on 2014-03-21
10
188 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Message not shown 5 49
Oracle Stored Procedure for User Account data 2 28
export sql results to csv 6 35
Oracle Query - Return results based on minimum value 8 31
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article will show, step by step, how to integrate R code into a R Sweave document
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

776 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