Home » SQL & PL/SQL » SQL & PL/SQL » missing values in database (oracle 10)
| missing values in database [message #571190] |
Thu, 22 November 2012 02:17  |
 |
Klausn
Messages: 10
Registered: November 2012
Location: Germany
|
Junior Member |
|
|
Hi,
i´ve got a table with colomns (time, value) ... for example:
time value
10:00 10
11:00 20
12:00 25
13:00 28
15:00 31
17:00 38
you can see, that the row 14:00 and 17:00 are missing. How i can create the missing values?
The value should be the value from the last point. The result should be this:
time value
10:00 10
11:00 20
12:00 25
13:00 28
14:00 28
15:00 31
16:00 31
17:00 38
How can i do this ?
|
|
|
|
| Re: missing values in database [message #571192 is a reply to message #571190] |
Thu, 22 November 2012 02:27  |
 |
Michel Cadot
Messages: 54236
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcom to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
You need to outer join your query with a row generator, for instance see date generator post.
Also, the same kind of question has been posted many times and not so far than last week, please search for "row generator" in this forum.
Regards
Michel
|
|
|
|
| Re: missing values in database [message #571214 is a reply to message #571192] |
Thu, 22 November 2012 03:55 |
|
Klausn
Messages: 10
Registered: November 2012
Location: Germany
|
Junior Member |
|
|
i´m a beginner in oracle ..
now i have a simple database query
select time, value
from table_name
where time between to_date('11/20/2012 10:00:00','MM/DD/YYYY HH24:MI:SS')
and to_date('11/20/2012 17:00:00','MM/DD/YYYY HH24:MI:SS')
time value
10:00 10
11:00 20
12:00 25
13:00 28
15:00 31
17:00 38
but i want this as result:
time value
10:00 10
11:00 20
12:00 25
13:00 28
14:00 28
15:00 31
16:00 31
17:00 38
I readed your links .. but im not sure what i must use to solve this problem.
[Updated on: Thu, 22 November 2012 10:56] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: missing values in database [message #571236 is a reply to message #571216] |
Thu, 22 November 2012 06:12 |
|
Klausn
Messages: 10
Registered: November 2012
Location: Germany
|
Junior Member |
|
|
its a bit difficult to write what you want ... because i use crystal reports.
Now i have two tables. One time table with query
select column_value time from table
(timetable.hour (trunc(to_date('11/20/2012 10:00:00','MM/DD/YYYY HH24:MI:SS'),
trunc(to_date('11/20/2012 17:00:00','MM/DD/YYYY HH24:MI:SS'))
with a left join to the query
select time, value
from table_name
where time between to_date('11/20/2012 10:00:00','MM/DD/YYYY HH24:MI:SS')
and to_date('11/20/2012 17:00:00','MM/DD/YYYY HH24:MI:SS')
i get:
time value
10:00 10
11:00 20
12:00 25
13:00 28
14:00
15:00 31
16:00
17:00 38
and now i need to fill these two ISNULL values with values.
im sure that it i could all in one sql statement, but i dont know how ...
[Updated on: Thu, 22 November 2012 10:56] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
 Re: missing values in database [message #571282 is a reply to message #571190] |
Fri, 23 November 2012 00:02 |
 |
tony123
Messages: 9
Registered: August 2012
|
Junior Member |
|
|
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as test@ora11g_231
SQL> col time format a6
SQL> col old_value format a6
SQL>
SQL> DROP TABLE tt;
Table dropped
SQL> CREATE TABLE tt (TIME INT, VALUE INT);
Table created
SQL> insert into tt values(10, 10);
1 row inserted
SQL> insert into tt values(11, 20);
1 row inserted
SQL> insert into tt values(12, 25);
1 row inserted
SQL> insert into tt values(13, 28);
1 row inserted
SQL> insert into tt values(15, 31);
1 row inserted
SQL> insert into tt values(17, 38);
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT to_char(X.T)||':00' TIME,
2 DECODE(VALUE, NULL, LAG(VALUE) OVER(ORDER BY NULL), VALUE) VALUE,
3 TT.TIME OLD_TIME,
4 TT.VALUE OLD_VALUE
5 FROM TT,
6 (SELECT L + LEVEL - 1 T
7 FROM DUAL, (SELECT MAX(TIME) H, MIN(TIME) L FROM TT)
8 CONNECT BY LEVEL <= H - L + 1) X
9 WHERE TT.TIME(+) = X.T
10 ORDER BY 1;
TIME VALUE OLD_TIME OLD_VA
------ ---------- --------------------------------------- ------
10:00 10 10 10
11:00 20 11 20
12:00 25 12 25
13:00 28 13 28
14:00 28
15:00 31 15 31
16:00 31
17:00 38 17 38
8 rows selected
SQL> DROP TABLE tt;
Table dropped
SQL> CREATE TABLE tt (TIME varchar2(20), VALUE INT);
Table created
SQL> insert into tt values('10:00', 10);
1 row inserted
SQL> insert into tt values('11:00', 20);
1 row inserted
SQL> insert into tt values('12:00', 25);
1 row inserted
SQL> insert into tt values('13:00', 28);
1 row inserted
SQL> insert into tt values('15:00', 31);
1 row inserted
SQL> insert into tt values('17:00', 38);
1 row inserted
SQL> COMMIT;
Commit complete
SQL> WITH a AS(SELECT to_number(SUBSTR(TIME,1,2)) TIME,VALUE from tt)
2 SELECT to_char(X.T)||':00' TIME,
3 DECODE(VALUE, NULL, LAG(VALUE) OVER(ORDER BY NULL), VALUE) VALUE,
4 a.TIME OLD_TIME,
5 a.VALUE OLD_VALUE
6 FROM a,
7 (SELECT L + LEVEL - 1 T
8 FROM DUAL, (SELECT MAX(TIME) H, MIN(TIME) L FROM a)
9 CONNECT BY LEVEL <= H - L + 1) X
10 WHERE a.TIME(+) = X.T
11 ORDER BY T;
TIME VALUE OLD_TIME OLD_VA
------ ---------- ---------- ------
10:00 10 10 10
11:00 20 11 20
12:00 25 12 25
13:00 28 13 28
14:00 28
15:00 31 15 31
16:00 31
17:00 38 17 38
8 rows selected
[Updated on: Fri, 23 November 2012 00:08] Report message to a moderator |
|
|
|
| Re: missing values in database [message #571296 is a reply to message #571282] |
Fri, 23 November 2012 01:38 |
|
Michel Cadot
Messages: 54236
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please read the link I provided, it provides a correct answer.
Your "solution" is conceptually wrong and may give the correct output only by chance, see I just add one line in your test (and remove the unwanted column output):
SQL> CREATE TABLE tt (TIME varchar2(20), VALUE INT);
Table created.
SQL> insert into tt values('10:00', 10);
1 row created.
SQL> insert into tt values('11:00', 20);
1 row created.
SQL> insert into tt values('12:00', 25);
1 row created.
SQL> insert into tt values('13:00', 28);
1 row created.
SQL> insert into tt values('15:00', 31);
1 row created.
SQL> insert into tt values('17:00', 38);
1 row created.
SQL> insert into tt values('20:00', 10);
1 row created.
SQL> col time format a5
SQL> WITH a AS(SELECT to_number(SUBSTR(TIME,1,2)) TIME,VALUE from tt)
2 SELECT to_char(X.T)||':00' TIME,
3 DECODE(VALUE, NULL, LAG(VALUE) OVER(ORDER BY NULL), VALUE) VALUE
4 FROM a,
5 (SELECT L + LEVEL - 1 T
6 FROM DUAL, (SELECT MAX(TIME) H, MIN(TIME) L FROM a)
7 CONNECT BY LEVEL <= H - L + 1) X
8 WHERE a.TIME(+) = X.T
9 ORDER BY T;
TIME VALUE
----- ----------
10:00 10
11:00 20
12:00 25
13:00 28
14:00 28
15:00 31
16:00 31
17:00 38
18:00 38
19:00
20:00 10
Regards
Michel
|
|
|
|
| Re: missing values in database [message #571301 is a reply to message #571296] |
Fri, 23 November 2012 02:14 |
|
Michel Cadot
Messages: 54236
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Modifying your query for it to work and adding a flag showing the original values:
SQL> WITH a AS(SELECT to_number(SUBSTR(TIME,1,2)) TIME,VALUE from tt)
2 SELECT to_char(X.T)||':00' TIME,
3 last_value(VALUE ignore nulls) OVER(ORDER BY x.t) VALUE,
4 decode(value, null, '', '<-- ') flag
5 FROM a,
6 (SELECT L + LEVEL - 1 T
7 FROM DUAL, (SELECT MAX(TIME) H, MIN(TIME) L FROM a)
8 CONNECT BY LEVEL <= H - L + 1) X
9 WHERE a.TIME(+) = X.T
10 ORDER BY T;
TIME VALUE FLAG
----- ---------- ----
10:00 10 <--
11:00 20 <--
12:00 25 <--
13:00 28 <--
14:00 28
15:00 31 <--
16:00 31
17:00 38 <--
18:00 38
19:00 38
20:00 10 <--
Regards
Michel
|
|
|
|
|
|
| Re: missing values in database [message #571647 is a reply to message #571302] |
Wed, 28 November 2012 08:16 |
|
Klausn
Messages: 10
Registered: November 2012
Location: Germany
|
Junior Member |
|
|
hi guys .. i have little problem with this solution.
what i must change to get values for every 10 minutes? or every 15 minutes!?
ah i think i know the problem ... the time value isnt a time format. It is an datetime format !!! How can i change this ?
[Updated on: Wed, 28 November 2012 08:40] Report message to a moderator |
|
|
|
|
|
| Re: missing values in database [message #571686 is a reply to message #571648] |
Thu, 29 November 2012 02:41 |
|
Klausn
Messages: 10
Registered: November 2012
Location: Germany
|
Junior Member |
|
|
Hi Michel,
i have tested but i get this result:
First we have a database (tt) and two tables (datetimes) and (value).
The simple query to get this is:
select datetimes, value from tt where id=12345 and datetimes between to_date('01/01/2012 08:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('01/01/2012 20:00:00', 'MM/DD/YYYY HH24:MI:SS')
DATETIMES VALUE
---------------- ----------
01.01.2012 08:00 67
01.01.2012 09:00 32
01.01.2012 10:00 10
01.01.2012 13:00 28
01.01.2012 14:00 28
01.01.2012 15:00 31
01.01.2012 17:00 38
01.01.2012 20:00 10
I use this sql query:
SQL> WITH a AS(SELECT to_number(SUBSTR(DATETIMES,1,2)) DATETIMES, VALUE from TT where id=12345 and DATETIMES between to_date('01/01/2012 08:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('01/01/2012 20:00:00', 'MM/DD/YYYY HH24:MI:SS'))
2 SELECT to_char(X.T)||':00' DATETIMES,
3 last_value(VALUE ignore nulls) OVER(ORDER BY x.t) VALUE,
4 decode(VALUE, null, '', '<-- ') flag
5 FROM a,
6 (SELECT L + LEVEL - 1 T
7 FROM DUAL, (SELECT MAX(DATETIMES) H, MIN(DATETIMES) L FROM a)
8 CONNECT BY LEVEL <= H - L + 1) X
9 WHERE a.DATETIMES(+) = X.T
10 ORDER BY T;
and i get:
DATETIMES VALUE FLAG
--------- ---------- ----
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
01:00 10 <--
but i want this:
DATETIMES VALUE
--------- ----------
01.01.2012 08:00 67
01.01.2012 09:00 32
01.01.2012 10:00 10
01.01.2012 11:00 10
01.01.2012 12:00 10
01.01.2012 13:00 28
01.01.2012 14:00 28
01.01.2012 15:00 31
01.01.2012 16:00 31
01.01.2012 17:00 38
01.01.2012 18:00 38
01.01.2012 19:00 38
01.01.2012 20:00 10
i dont know wheres the failure
[Updated on: Thu, 29 November 2012 02:44] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: missing values in database [message #571697 is a reply to message #571690] |
Thu, 29 November 2012 04:18 |
|
Klausn
Messages: 10
Registered: November 2012
Location: Germany
|
Junior Member |
|
|
have tryed but nothing happend.
is it possible that my where clause is on the wrong postion ?
(where id=12345 and DATETIMES between to_date('01/01/2012 08:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('01/01/2012 20:00:00', 'MM/DD/YYYY HH24:MI:SS')))
[Updated on: Thu, 29 November 2012 04:21] Report message to a moderator |
|
|
|
|
|
|
|
| Re: missing values in database [message #571707 is a reply to message #571705] |
Thu, 29 November 2012 05:37 |
|
Michel Cadot
Messages: 54236
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote: i cant create a test case
To create a test case you ONLY need your fingers to type the statements as tony123 did.
Through away TOAD and use SQL*Plus, you can't post a TOAD session, you can post a SQL*Plus one.
We have neither your table nor your data, so what you say is pointless for us to identify the problem.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Sat May 25 00:05:12 CDT 2013
Total time taken to generate the page: 0.11828 seconds
|
] 
Members
Search
Help
Register
Login
Home
