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'));
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'));
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?
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?
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
...
...
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
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.
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
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
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
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.
(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;
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
....
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
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.
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_V ALUE, 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..
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_V
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..
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.
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
LAST_VALUE(DECODE(COLUMN_V ALUE, 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
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
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
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
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)
TABLE(SELECT COLLECT(LEVEL - 1)
FROM DUAL
CONNECT BY prevf3 + LEVEL - 1 < f3)
ASKER
OK, thank you very much.
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