Home » SQL & PL/SQL » SQL & PL/SQL » help with outer join
help with outer join [message #291863] Mon, 07 January 2008 01:44 Go to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hello there, im trying to get an extra line with outer join,
but having one problem that i'd like to solve.

Here's the example.

first select returns:

datetime | key_id
02.08 11:00 15
02.08 11:15 15
02.08 11:30 15
02.08 11:45 15
02.08 12:00 15
02.08 12:15 15
02.08 12:30 15
02.08 12:45 15

second select returns datetime which minutes does not have to be full quartes (for some key_ids it is quarter+few minutes):

datetime | key_id | columns,...
02.08 11:02 15 ...
02.08 11:17 15
-- misisng
-- misisng
02.08 12:02 15
02.08 12:17 15
02.08 12:32 15
02.08 12:47 15

"Right outer join" second select with first select i can get those two extra lines, but i'd like to get there minutes in the same format as they are in the first select...

datetime | key_id | column1,...
02.08 11:02 15 ...
02.08 11:17 15
02.08 11:30 -----> i can get these extra lines. But i need 11:32
02.08 11:45 -----> and 11:47 time to be there.
02.08 12:02 15
02.08 12:17 15
02.08 12:32 15
02.08 12:47 15

Thanks for any suggestions
Re: help with outer join [message #291867 is a reply to message #291863] Mon, 07 January 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Then post a test case that we can reproduce: create table and insert statements.
Also always post your queries.

Are always your data 02, 07, 12, 17... or can it be 02, 08, 11? I mean is the interval always a multiple of 5 minutes?

Regards
Michel
Re: help with outer join [message #291884 is a reply to message #291863] Mon, 07 January 2008 02:26 Go to previous message
dusoo
Messages: 41
Registered: March 2007
Member
Hi,
additional minutes for one key_id are always the same.
If there is a slide of 3minutes for one key_id,
those 3minutes are in each quarter of that key_id
(00+3,15+3, 30+3, 45+3)

example
key_id = 15 => minutes = 02,17,32,47
key_id = 3 => minutes = 00,15,30,45
key_id = 12 => minutes = 04,19,34,49

I was thinking to solve it using analytical function LEAD on final select to get somehow the usual minute slide and add that "slide" to that each new datetime created by right outer join.

here is the sample code that returns also missing lines, but with unwanted truncated quartes...
WITH t AS
     (SELECT '2. 3. 2007 11:00' DATETIME, 15 key_id
        FROM DUAL
      UNION ALL
      SELECT '2. 3. 2007 11:15', 15
        FROM DUAL
      UNION ALL
      SELECT '2. 3. 2007 11:30', 15
        FROM DUAL
      UNION ALL
      SELECT '2. 3. 2007 11:45', 15
        FROM DUAL
      UNION ALL
      SELECT '2. 3. 2007 12:00', 15
        FROM DUAL
      UNION ALL
      SELECT '2. 3. 2007 12:15', 15
        FROM DUAL        ) ,
    t1 as
     (SELECT '2. 3. 2007 11:02' DATETIME, 15 key_id
        FROM DUAL
      UNION ALL
      SELECT '2. 3. 2007 11:17', 15
        FROM DUAL
      UNION ALL
      SELECT '2. 3. 2007 12:02', 15
        FROM DUAL
      UNION ALL        
      SELECT '2. 3. 2007 12:17', 15
        FROM DUAL)        
SELECT t.datetime, t1.datetime,t.key_id
  FROM t1
  right outer join t on (
  t.key_id=t1.key_id and 
  to_datE(t.datetime,'dd.mm.yyyy hh24:mi') =
    TRUNC(to_date(t1.datetime,'dd.mm.yyyy hh24:MI'), 'HH') 
    + FLOOR(ROUND((to_date(t1.datetime,'dd.mm.yyyy hh24:MI') 
    - TRUNC(to_date(t1.datetime,'dd.mm.yyyy hh24:MI'), 'HH')) / (1 / (24 * 4)), 3)) * (1 / (24 * 4)))

[Updated on: Mon, 07 January 2008 02:44] by Moderator

Report message to a moderator

Previous Topic: sequence in package
Next Topic: Public Synonyms
Goto Forum:
  


Current Time: Sat Dec 03 08:13:47 CST 2016

Total time taken to generate the page: 0.32143 seconds