Solved

Oracle 11g date query (another)

Posted on 2011-02-10
6
725 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 475 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 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