Solved

Pl/SQL or SQL

Posted on 2014-03-21
10
187 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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create calculation and case in query with times 13 24
rhino JavaScript import, load 25 67
Oracle query output question 4 36
Need help subtracting a value within my script 7 42
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

910 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

22 Experts available now in Live!

Get 1:1 Help Now