Oracle SQL query doesn't show correct value

Question

I have a table with following columns:

id
technology
name_event
time_event

I count the number of values in a table with the following query

    select  1, 'Folder' as "Technology", 'Status' as "Name_Event", count(dm1.id) as "number of items", max(TO_CHAR(TO_DATE('20000101','yyyymmdd')+(SYSDATE - dm1.time_event),'hh24:mi:ss')) as "Time in system" 
    from db dm1 join db dm2 on dm1.id = dm2.id
    where dm1.technology = 'Folder' and dm1.name_event = 'status1' and NOT EXISTS(SELECT 1 FROM db dm2 WHERE dm2.name_event = 'status2' and dm2.id = dm1.id)

I did following inserts

    INSERT INTO DB(id, technology, name_event, time_event) VALUES(1,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(2,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(3,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(4,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(5,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(6,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(7,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(8,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(9,'Folder', 'status1', 01:00:00);
    INSERT INTO DB(id, technology, name_event, time_event) VALUES(10,'Folder', 'status1', 01:00:00);
INSERT INTO DB(id, technology, name_event, time_event) VALUES(4,'Folder', 'status2', 02:00:00);
INSERT INTO DB(id, technology, name_event, time_event) VALUES(1,'Folder', 'status2', 03:00:00);

I inserted 10 rows in status 1 and moved 2 to status 2. So there should be 8 items in status 1 when I execute the query, but the result is 10...

What am I doing wrong?

How about providing those 10 insert statements too along with the create table script? — Lalit Kumar B
– Lalit Kumar B, Commented Apr 30, 2015 at 8:47
Unless we have your sample data, it's going to be difficult for us to see what you're doing wrong - can you add the table definitions and the insert statements? — ninesided
– ninesided, Commented Apr 30, 2015 at 8:47
can you please provide some sample data in you table, then we can provide answer more quickly — Fathah Rehman P
– Fathah Rehman P, Commented Apr 30, 2015 at 8:47
are you running the insert, move commands and running the sql in same session ? if you don't do a commit and run the sql in other session it will still show the old value. * Just checking here. — anudeepks
– anudeepks, Commented Apr 30, 2015 at 8:56
Are you sure the problem is in the query? How did you move the values? — pablomatico
– pablomatico, Commented Apr 30, 2015 at 8:57

Captain · Accepted Answer · 2015-04-30 10:44:57Z

1

In your query, you are joining dm2 but then using a NOT EXISTS correlated subquery (and using the same alias inside that subquery). I can't replicate the problem with my setup, but that could sure confuse something! So try without that second copy of db, and just keep the NOT EXISTS :

select  1, 'Folder' as "Technology", 'Status' as "Name_Event", count(dm1.id) as "number of items", max(TO_CHAR(TO_DATE('20000101','yyyymmdd')+(SYSDATE - dm1.time_event),'hh24:mi:ss')) as "Time in system" 
from db dm1 
where dm1.technology = 'Folder' and dm1.name_event = 'status1' and NOT EXISTS(SELECT 1 FROM db dm2 WHERE dm2.name_event = 'status2' and dm2.id = dm1.id)

answered Apr 30, 2015 at 10:44

Captain

2,28817 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Captain Over a year ago

What do you get if you do a SELECT COUNT(*) from db dm1 where dm1.technology = 'Folder' and dm1.name_event = 'status1'? Or what about doing a SELECT * FROM ... - it seems others have equal difficulty recreating, which could suggest something funny going on in your data

Jonas Over a year ago

at least it worked now by your code, I don't know why it didn't first, maybe a commit that needed to take place. Thanks!

anudeepks · Accepted Answer · 2015-04-30 09:07:03Z

0

You are using 2 more inserts into the table, so now you have 10+2 total 12 inserts , to move data, use update statement.

update db set name_event='status2' where id=4;
update db set name_event='status4' where id=1;

edited Apr 30, 2015 at 9:07

answered Apr 30, 2015 at 9:02

anudeepks

1,1321 gold badge14 silver badges26 bronze badges

3 Comments

Jonas Over a year ago

I can't use an update because I need to keep my original rows ;-)

anudeepks Over a year ago

@Guil , can you pls elaborate on your requirements? if you keep the original rows, you will end up with both the data, duplicate for id, else you can keep a trigger and move the data to another table which will hold the old data and this table will reflect the new changes

Jonas Over a year ago

my requirements are that I make inserts when a status change happens. After this I make a lot of analytics because it's a monitoring table. Using a duplicate table is silly because in practise there are more than 10 states, but I simplify my problem.

Collectives™ on Stack Overflow

Oracle SQL query doesn't show correct value

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related