?
Solved

Oracle 11g date query (another)

Posted on 2011-02-10
6
Medium Priority
?
743 Views
Last Modified: 2013-11-11
I have this Oracle table:
test2:
select * from test2 order by f0, f1, f3
F0  F1    F2    F3
====================
A      X      7      6/10/2010
A      X      10      6/12/2010
A      Y      20      6/11/2010
A      Y      40      6/17/2010
A      Y      400      6/20/2010
B      P      7      6/10/2010
B      P      10      6/12/2010
B      Q      20      6/11/2010
B      Q      40      6/17/2010
B      Q      400      6/20/2010
C      R      7      6/10/2010
C      R      10      6/12/2010
C      S      20      6/11/2010
C      S      40      6/17/2010
C      S      400      6/20/2010


I want this O/P:
F0      F1       F2      F3
=================================
A      X      7      6/10/2010
A      X      7      6/11/2010
A      X      17      6/12/2010
A      Y      20      6/11/2010
A      Y      20      6/12/2010
A      Y      20      6/13/2010
A      Y      20      6/14/2010
A      Y      20      6/15/2010
A      Y      20      6/16/2010
A      Y      60      6/17/2010
A      Y      60      6/18/2010
A      Y      60      6/19/2010
A      Y      460      6/20/2010
B      P      7      6/10/2010
B      P      7      6/11/2010
B      P      17      6/12/2010
B      Q      20      6/11/2010
B      Q      20      6/12/2010
B      Q      20      6/13/2010
B      Q      20      6/14/2010
B      Q      20      6/15/2010
B      Q      20      6/16/2010
B      Q      60      6/17/2010
B      Q      60      6/18/2010
B      Q      60      6/19/2010
B      Q      460      6/20/2010
C      R      7      6/10/2010
C      R      7      6/11/2010
C      R      17      6/12/2010
C      S      20      6/11/2010
C      S      20      6/12/2010
C      S      20      6/13/2010
C      S      20      6/14/2010
C      S      20      6/15/2010
C      S      20      6/16/2010
C      S      60      6/17/2010
C      S      60      6/18/2010
C      S      60      6/19/2010
C      S      460      6/20/2010

I tried this code:
SELECT LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f0) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f0,
      LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f1) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f1,
       LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f2) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f2,
       f3 - COLUMN_VALUE f3
  FROM (SELECT t.*, LAG(f3) OVER (PARTITION BY f0, f1 ORDER BY f3) prevf3
          FROM test2 t) x,
       TABLE(SELECT COLLECT(LEVEL - 1)
               FROM DUAL
             CONNECT BY prevf3 + LEVEL - 1 < f3)
ORDER BY f0,f1,f3

But it is returning no value.
It is similar to the qn at http://www.experts-exchange.com/Database/Oracle/Q_26811329.html#a34868122


create table test2(f0 varchar2(100), f1 varchar2(100), f2 number(10), f3 date);
insert into test2 values ('A',  'X', 7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test2 values ('A',  'X', 10, to_date('06/12/2010', 'mm/dd/yyyy'));
insert into test2 values ('A',  'Y', 20, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test2 values ('A',  'Y', 40, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test2 values ('A',  'Y', 400, to_date('06/20/2010', 'mm/dd/yyyy'));

insert into test2 values ('B',  'P', 7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test2 values ('B',  'P', 10, to_date('06/12/2010', 'mm/dd/yyyy'));
insert into test2 values ('B',  'Q', 20, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test2 values ('B',  'Q', 40, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test2 values ('B',  'Q', 400, to_date('06/20/2010', 'mm/dd/yyyy'));

insert into test2 values ('C',  'R', 7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test2 values ('C',  'R', 10, to_date('06/12/2010', 'mm/dd/yyyy'));
insert into test2 values ('C',  'S', 20, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test2 values ('C',  'S', 40, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test2 values ('C',  'S', 400, to_date('06/20/2010', 'mm/dd/yyyy'));
commit;

Open in new window

0
Comment
Question by:toooki
[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
  • 4
  • 2
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1900 total points
ID: 34870294
you need an analytic sum on f2 first, then apply the rest of the logic as before


SELECT LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f0) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f0,
       LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f1) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f1,
       LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f2) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f2,
       f3 - COLUMN_VALUE f3
  FROM (SELECT t.*, LAG(f3) OVER (PARTITION BY f0, f1 ORDER BY f3) prevf3
          FROM (SELECT f0,
                       f1,
                       SUM(
                           f2
                       )
                       OVER (
                           PARTITION BY f0, f1
                           ORDER BY f3
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       )
                           f2,
                       f3
                  FROM test2) t) x,
       TABLE(SELECT COLLECT(LEVEL - 1)
               FROM DUAL
             CONNECT BY prevf3 + LEVEL - 1 < f3)
ORDER BY f0, f1, f3
0
 

Author Comment

by:toooki
ID: 34881141
Thank you. I am testing the query.
0
 

Author Comment

by:toooki
ID: 34881315
Thank you. I think I am getting the correct O/P. But could you please let me know these...
I understand:
select level from dual connect by level < 10 Returns 1,2,3 ..9.
         
prevf3 is of DATE data type. How does this part of the below code work:
prevf3 + LEVEL - 1
Why LEVEL - 1 ?

  TABLE(SELECT COLLECT(LEVEL - 1)
               FROM DUAL
             CONNECT BY prevf3 + LEVEL - 1 < f3)

Here is three consecutive row entry (part of) o/p of attached code:
f1      f2     f3              prevf3
B      7      6/10/2010      (NULL)
B      338      6/11/2010      6/10/2010
B      426      6/17/2010      6/11/2010

What is the select value of the above statement for these rows? And what the TABLE keyword is doing?
I am referring to the test1 table:
select * from test1:
f1      f2     f3
A      7      6/10/2010
A      338      6/11/2010
A      426      6/17/2010
B      7      6/10/2010
B      338      6/11/2010
B      426      6/17/2010
C      7      6/10/2010
C      338      6/11/2010
C      426      6/17/2010



SELECT t.*, LAG(f3) OVER (PARTITION BY f1 ORDER BY f3) prevf3
          FROM test1 t

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:toooki
ID: 34881321
Also for this part of code:
  FROM (SELECT t.*, LAG(f3) OVER (PARTITION BY f1 ORDER BY f3) prevf3
          FROM test1 t) x,
 TABLE(SELECT COLLECT(LEVEL - 1)
              FROM DUAL
            CONNECT BY prevf3 + LEVEL - 1 < f3)

Are we "joining" x with
 TABLE(SELECT COLLECT(LEVEL - 1)
              FROM DUAL
            CONNECT BY prevf3 + LEVEL - 1 < f3)

without where clause?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34882646
The level -1 is because we don't really want 1-10,  we want 0-9

the f3 column in the final results is calculated by     f3 - COLUMN_VALUE ,  so,  if we used 1-10,  the end date of each range would be excluded,  f3-1,  but f3-0 = f3, so we get the entire range.
I could have just as easily left it LEVEL,  and then put the -1 on the calculation as "f3 - (column_value - 1)"

--------

compare these results of these two...

select level from dual connect by level <= 10;
select collect(level) from dual connect by level <= 10;

The table function lets us turn the collection into a set we can join against.

-----------------

The select from test table is this, to generate the rolling totals for each date


(SELECT f0,
                       f1,
                       SUM(
                           f2
                       )
                       OVER (
                           PARTITION BY f0, f1
                           ORDER BY f3
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       )
                           f2,
                       f3
                  FROM test2)


----------------------------------

The join to the TABLE(....)  function does not have a WHERE clause, but it does have a filtering/join clause determined by the CONNECT BY

You'll see the connect by range isn't a fixed 1-10,  the numeric range is determined by the number of days between prevf3 and f3






0
 

Author Comment

by:toooki
ID: 34903521
Many thanks for the explanations. It took me long to understand. I never learnt this much from a single query.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

801 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