Link to home
Start Free TrialLog in
Avatar of toooki
toooki

asked on

Oracle 11g date query

I have this Oracle table:
MyTab1:
F1            F2      F3
=========================
TEST1      7      6/10/2010
TEST1      338      6/12/2010
TEST1      426      6/17/2010

How can I query the table to have all the date values for F3 column so that the O/P looks like this:
F1            F2      F3
=========================
TEST1      7      6/10/2010
TEST1      7      6/11/2010
TEST1      338      6/12/2010
TEST1      338      6/13/2010
TEST1      338      6/14/2010
TEST1      338      6/15/2010
TEST1      338      6/16/2010
TEST1      426      6/17/2010

O/P will have all "missing dates" in between. And the O/P F2 field will be the last latest F2 field value's content.

Table create script:
create table test1(f1 varchar2(100), f2 number(10), f3 date);
insert into test1 values ('TEST1',  7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test1 values ('TEST1',  338, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test1 values ('TEST1',  426, to_date('06/17/2010', 'mm/dd/yyyy'));
Avatar of Sean Stuber
Sean Stuber

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

ASKER

Thank you. It works but a bit difficult for me to understand.
A quick question, if there is a date dimension table:
DateDim
=====
Jan-01-2001
Jan-02-2001
Jan-03-2001
...
Dec-31-2015

Could the above O/P be obtained with some less complex query with a join between MyTab1 and DateDim?
Avatar of toooki

ASKER

Also when I replace "test1" in your query above with the query that actually create a table similar to "test1" -- The output of the entire query is missing some F3 values.
It is hard for me to troubleshoot as I cannot understand the meaning some part of the query..

Can I write a join clause like:

select p.f1, p.f2, q.date
FROM test1 p, DateDim q
where p.f3(+) = q.date
...
...
I think you can  go with sdstuber solution but you can check this.

WITH cte 
     AS (SELECT DateCol,F1, F2, F3 
           FROM DateDim t1 
                LEFT JOIN MyTab1 t2 
                  ON t1.DateCol = t2.F3) 
SELECT F1, 
       (SELECT MAX(F2) 
          FROM cte c2 
         WHERE c2.F2 IS NOT NULL 
               AND c2.DateCol <= c1.DateCol) F2, 
       (SELECT MAX(F3) 
          FROM cte c2 
         WHERE c2.F2 IS NOT NULL 
               AND c2.DateCol <= c1.DateCol) F3 
  FROM cte c1

Open in new window

per-  http:#34859531

the query I posted was tested and produced the requested results.
if it didn't work with your changes, are you sure you didn't introduce something?

post what your final version looked like.
as for the datedim table, yes you could use that instead, but why do extra io if you don't have to?


SELECT   DISTINCT
         LAST_VALUE(
             t.f1)
         OVER (PARTITION BY d.datedim
               ORDER BY t.f3
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
              )
             f1,
         LAST_VALUE(
             t.f2)
         OVER (PARTITION BY d.datedim
               ORDER BY t.f3
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
              )
             f2,
         d.datedim
    FROM test1 t, datedim d
   WHERE d.datedim >= t.f3 AND d.datedim <= (SELECT MAX(f3) FROM test1)
ORDER BY datedim
is there something in particular about my original solution you don't understand? I'm happy to explain any pieces of it you'd like.

I specifically did NOT use an extra table or do self joins or sub-queries back to the original table because that simply adds a lot of io and extra operations, when a little sorting is all that is required.

of course, with any timing claim,  test, test, test,  and measure, don't trust me.

as the volume of data increases, the relative resource consumption and timing of sorting vs extra io should become very obvious
Avatar of toooki

ASKER

Thank you all. My table content is like this:
(select f1, f3, f2 from test1 order by f1, f3;)
F1    F3             F2
===============
A      6/10/2010      7
A      6/11/2010      338
A      6/17/2010      426
B      6/10/2010      7
B      6/11/2010      338
B      6/17/2010      426
C      6/10/2010      7
C      6/11/2010      338
C      6/17/2010      426

Attached the code to create the table and its content.

If I use the query that sdstuber mentioned in the first post, the O/P becomes:
B      7      6/10/2010
A      7      6/10/2010
C      7      6/10/2010
B      338      6/11/2010
A      338      6/11/2010
C      338      6/11/2010
C      338      6/12/2010
C      338      6/13/2010
C      338      6/14/2010
C      338      6/15/2010
C      338      6/16/2010
B      426      6/17/2010
C      426      6/17/2010
A      426      6/17/2010

The above O/P is correct when F1 field's value is C. For F1 field's value of A or B , O/P has missing dates (like 6/12/2010, 6/13/2010...)

I cannot rewrite the query to O/P order by f1, f3.



drop table test1;
create table test1(f1 varchar2(100), f2 number(10), f3 date);
insert into test1 values ('A',  7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test1 values ('A',  338, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test1 values ('A',  426, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test1 values ('B',  7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test1 values ('B',  338, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test1 values ('B',  426, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test1 values ('C',  7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test1 values ('C',  338, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test1 values ('C',  426, to_date('06/17/2010', 'mm/dd/yyyy'));
commit;

Open in new window

Avatar of toooki

ASKER

I get no results from the attached query that Sharath_123 mentioned to me:

Here is the output of the gmtb_timedim table:
select dt_date from gmtb_timedim where dt_date > to_date('06/01/2010', 'mm/dd/yyyy')
and dt_date <= to_date('06/30/2010', 'mm/dd/yyyy')
6/2/2010
6/3/2010
6/4/2010
6/5/2010
6/6/2010
6/7/2010
6/8/2010
6/9/2010
6/10/2010
6/11/2010
6/12/2010
6/13/2010
6/14/2010
6/15/2010
6/16/2010
6/17/2010
6/18/2010
6/19/2010
6/20/2010
6/21/2010
6/22/2010
6/23/2010
6/24/2010
6/25/2010
6/26/2010
6/27/2010
6/28/2010
6/29/2010
6/30/2010

SELECT DISTINCT
         LAST_VALUE(
             t.f1)
         OVER (PARTITION BY d.dt_date
               ORDER BY t.f3
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
              )
             f1,
         LAST_VALUE(
             t.f2)
         OVER (PARTITION BY d.dt_date
               ORDER BY t.f3
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
              )
             f2,
         d.dt_date
    FROM test1 t, gmtb_timedim  d
   WHERE d.dt_date >= t.f3 AND d.dt_date <= (SELECT MAX(f3) FROM test1)
ORDER BY dt_date

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of toooki

ASKER

Sorry in last post I commented about the post made by sdstuber.

 Also I get all null records with this query that Sharath_123 mentioned above.

WITH cte
     AS (SELECT dt_date,F1, F2, F3
           FROM gmtb_timedim  t1
                LEFT JOIN test1 t2
                  ON t1.dt_date = t2.F3)
SELECT F1,
       (SELECT MAX(F2)
          FROM cte c2
         WHERE c2.F2 IS NOT NULL
               AND c2.dt_date <= c1.dt_date) F2,
       (SELECT MAX(F3)
          FROM cte c2
         WHERE c2.F2 IS NOT NULL
               AND c2.dt_date <= c1.dt_date) F3
  FROM cte c1
Avatar of toooki

ASKER

WITH cte
....

I may not be able to use the above command. I need to create a view from the output of what I intend to get here.
Create view MyView as select * from ....
If I have any WITH cte  caluse in the select clause .. I get error.
Did you get the result after adding an additional filter?
WITH cte 
     AS (SELECT dt_date,F1, F2, F3 
           FROM gmtb_timedim  t1 
                LEFT JOIN test1 t2 
                  ON t1.dt_date = t2.F3) 
SELECT F1, 
       (SELECT MAX(F2) 
          FROM cte c2 
         WHERE c2.F2 IS NOT NULL 
               AND c2.dt_date <= c1.dt_date and c2.F1 = c1.F1) F2, 
       (SELECT MAX(F3) 
          FROM cte c2 
         WHERE c2.F2 IS NOT NULL 
               AND c2.dt_date <= c1.dt_date and c2.F1 = c1.F1) F3 
  FROM cte c1

Open in new window

If you need help with the other query I posted that uses 2 tables, I can, but it's much more efficient to only query 1 table and only once.
Avatar of toooki

ASKER

Thanks all for help. I still get null with Sharath_123's query.

The last query sdstuber works perfectly. But I do not understand the structure of the query at all.

I understand what this is doing:
SELECT t.*, LAG(f3) OVER (PARTITION BY f1 ORDER BY f3) prevf3 FROM test1 t

The query starts with:
SELECT LAST_VALUE(DECODE(COLUMN_VALUE, 0, f1) IGNORE NULLS)
       OVER ( PARTITION BY

I do not understand above. I know DECODE function.

I do not understand this as well:
SELECT COLLECT(LEVEL - 1)
               FROM DUAL
             CONNECT BY prevf3 + LEVEL - 1 < f3

Do not understand which ones are oracle keywords and which ones are variable name you give.

Will really appreciate if you tell me where to get the tutorial to understand the above.

The query runs very fast. I really need to understand a bit of above because I need to run similar queries which are not absolutely same as above..

Avatar of toooki

ASKER

If using the gmtb_timedim makes the query a bit more understandable, I would like that query..
Sharath_123's above query runs but I get all null values in O/P.
select dt_date from gmtb_timedim where dt_date > to_date('06/01/2010', 'mm/dd/yyyy')
and dt_date <= to_date('06/30/2010', 'mm/dd/yyyy')
6/2/2010
6/3/2010
6/4/2010
6/5/2010
6/6/2010
6/7/2010
6/8/2010
6/9/2010
6/10/2010
6/11/2010
6/12/2010
6/13/2010
6/14/2010
6/15/2010
6/16/2010
6/17/2010
6/18/2010
6/19/2010
6/20/2010
6/21/2010
6/22/2010
6/23/2010
6/24/2010
6/25/2010
6/26/2010
6/27/2010
6/28/2010
6/29/2010
6/30/2010

Open in new window

LAST_VALUE(DECODE(COLUMN_VALUE, 0, f1) IGNORE NULLS)
       OVER ( PARTITION BY


the LAST_VALUE function is an analytic like LAG or LEAD,  
LAST_VALUE, as the name implies returns the value of the last row within the partition.   So,  since rows are sorted by date,  the value of the 6/17/2010  row is the value that will be returned.

DECODE(column_value, 0, f1)  

this means if the column called "column_value"  equal 0  then return f1, otherwise return NULL

use this query (without the analytics)  to see the pattern of the rows and their respective values

select x.*, column_value, decode(column_value,0,f1)
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)
ORDER BY f1,f3


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


this part is a little tricky.  

here's a simplified version

select level from dual connect by level < 10

the simple version generates 10 numbers

I want to do do the same thing but generate numbers to correspond to the number of days between the row's date and the previous row's date.

So, for 6/11/2010 to 6/17/2010  I would want 7 numbers 0-6

COLLECT pulls all of them into a collection and the TABLE function allows the collection to be returned as a result set that can be joined to the master table

Avatar of toooki

ASKER

Thank you. I still have small question:

Where do you define "COLUMN_VALUE"?

The main SELECT query is like:

SELECT f1, f2, f3 - COLUMN_VALUE f3
FROM ..
ORDER BY

The content inside 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)
------------
The above part cannot be run as an independent SQL query. I wanted to see what O/P it gets.

I need to write similar query with 4 columns. One extra f0 column and the partition is based on f0, f1 combination. But my updated query returns null. Will appreciate if you could look into the query that I wrote for it at
https://www.experts-exchange.com/questions/26814170/Oracle-11g-date-query-another.html
column_value is an automatically generated name from this


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


Avatar of toooki

ASKER

OK, thank you very much.