Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle 11g date query (another)

Posted on 2011-02-10
6
Medium Priority
?
767 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
Independent Software Vendors: 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!

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

618 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