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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
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 Go to previous messageGo to next message
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 #571216 is a reply to message #571214] Thu, 22 November 2012 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Post what I requested
2/ Post what you tried.

Regards
Michel
Re: missing values in database [message #571236 is a reply to message #571216] Thu, 22 November 2012 06:12 Go to previous messageGo to next message
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 #571239 is a reply to message #571236] Thu, 22 November 2012 06:47 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use nvl
Re: missing values in database [message #571240 is a reply to message #571236] Thu, 22 November 2012 06:49 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
You can use NVL function to value column:

NVL(value,-99) 
/* -99 is just an example, Replace it with your action */


Regards,
Veeresh
Re: missing values in database [message #571243 is a reply to message #571236] Thu, 22 November 2012 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
its a bit difficult to write what you want ... because i use crystal reports.


You only need at most notepad to write what I asked. You can even directly write it your post when you reply.

Quote:
and now i need to fill these two ISNULL values with values.


Use LAST_VALUE function with IGNORE NULLS option.
And if you searched for what I told you already got the answer as this the same question I andswered last week.

Regards
Michel


Re: missing values in database [message #571260 is a reply to message #571243] Thu, 22 November 2012 09:54 Go to previous messageGo to next message
Klausn
Messages: 10
Registered: November 2012
Location: Germany
Junior Member
have to try it... thanks.
Re: missing values in database [message #571261 is a reply to message #571260] Thu, 22 November 2012 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/184935/570775/102589/#msg_570775

Regards
Michel
icon1.gif  Re: missing values in database [message #571282 is a reply to message #571190] Fri, 23 November 2012 00:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
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 #571302 is a reply to message #571301] Fri, 23 November 2012 02:55 Go to previous messageGo to next message
Klausn
Messages: 10
Registered: November 2012
Location: Germany
Junior Member
hi guys .. many thanks .. i try this solution on weekend.
Re: missing values in database [message #571647 is a reply to message #571302] Wed, 28 November 2012 08:16 Go to previous messageGo to next message
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 #571648 is a reply to message #571647] Wed, 28 November 2012 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a little bit of maths.
If you want every 10 minutes then you have 60/10=6 times more lines to generate.
If you want every 15 minutes then you have 60/15=4 times more lines to generate.
The number of minutes is then not always 00 but 10*mod(X.T,6) or 15*mod(X.T,4) and the hour is trunc(X.T/6) or trunc(X.T/4)...

Try and come back with your tries.

Regards
Michel
Re: missing values in database [message #571686 is a reply to message #571648] Thu, 29 November 2012 02:41 Go to previous messageGo to next message
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 #571687 is a reply to message #571686] Thu, 29 November 2012 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We can only work with what we have. Your table is not ours. So different tables, different results, few surprises. And you don't want to post a test case for what you have...

Add "/*+ materialize */" hint (without the quote) in your WITH subquery (just after SELECT and before "to_number").

Regards
Michel
Re: missing values in database [message #571688 is a reply to message #571687] Thu, 29 November 2012 03:04 Go to previous messageGo to next message
Klausn
Messages: 10
Registered: November 2012
Location: Germany
Junior Member
ok thanks .. i create a test case on weekend, then we could find the problem ... thanks a lot.
Re: missing values in database [message #571690 is a reply to message #571688] Thu, 29 November 2012 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try this:

Quote:
Add "/*+ materialize */" hint (without the quote) in your WITH subquery (just after SELECT and before "to_number").


Regards
Michel
Re: missing values in database [message #571697 is a reply to message #571690] Thu, 29 November 2012 04:18 Go to previous messageGo to next message
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 #571700 is a reply to message #571697] Thu, 29 November 2012 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know, I can't read not formatted queries.
See the difference bewteen your "a" query and what I posted.

In addition, do not just tell us what you did, SHOW us: copy and paste your SQL*Plus session.

Regards
Michel
Re: missing values in database [message #571705 is a reply to message #571700] Thu, 29 November 2012 05:28 Go to previous messageGo to next message
Klausn
Messages: 10
Registered: November 2012
Location: Germany
Junior Member
i think i found the problem. The format of the DATETIMES table is ('01/01/2012 08:00:00', 'MM/DD/YYYY HH24:MI:SS') ... i think it cant work with:

to_number(substr(datetimes,1,2)) ... i get only the number 27

BTW: i didnt use sql plus .. i use toad for oracle.
and here i cant create a test case .. i must do this with my own pc
Re: missing values in database [message #571707 is a reply to message #571705] Thu, 29 November 2012 05:37 Go to previous message
Michel Cadot
Messages: 59136
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
Previous Topic: Creating and refreshing materialized view group from another schema
Next Topic: balance of stock amount
Goto Forum:
  


Current Time: Thu Sep 18 16:46:30 CDT 2014

Total time taken to generate the page: 0.05852 seconds