[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Pl/SQL or SQL

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
marian68
Asked:
marian68
  • 6
  • 2
  • 2
1 Solution
 
awking00Commented:
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
 
marian68Author Commented:
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
 
awking00Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
marian68Author Commented:
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
 
flow01Commented:
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
 
marian68Author Commented:
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
 
marian68Author Commented:
I checked also only for the 3 products I sent you.
It doesn't work.
0
 
flow01Commented:
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
 
marian68Author Commented:
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
 
marian68Author Commented:
Thank you
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now