Home » SQL & PL/SQL » SQL & PL/SQL » Query data rows
Query data rows [message #271607] |
Tue, 02 October 2007 03:13 |
legna
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
Supposed i have a table table_t which contains the data:
ID OCDATE TC
1 9/26/2006 CB
1 9/26/2006 UT
2 9/26/2006 CS
2 9/26/2006 UT
3 9/26/2006 CS
4 9/26/2006 CB
5 9/26/2006 CS
4 9/26/2006 SS
5 9/26/2006 SS
6 9/26/2006 SS
7 9/26/2006 SS
And i want to get the following output
However, i can't seem to get the above results from my code:
select
a.id
from
table_t a
where
a.ocdate >= to_date('9/26/2006','mm/dd/yyyy') and
a.ocdate <= to_date('9/27/2007','mm/dd/yyyy') and
not exists
(select null
from dual b
where
a.ocdate >= to_date('9/26/2006','mm/dd/yyyy') and
a.ocdate <= to_date('9/27/2007','mm/dd/yyyy') and
a.tc not in ('CB', 'CS', 'UT')
)
It returns this instead:
Can anyone kindly help point out the logical error?
Thanks a million!
|
|
|
Re: Query data rows [message #271611 is a reply to message #271607] |
Tue, 02 October 2007 03:29 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It would have helped if you'd posted some create table/insert statements, and actually bothered to explain the process behind selecting the data you were looking for.
Fortunately I'm feeling generous, so I did all that work for you. The invoice is in the post.
Your mistake was that your NOT EXISTS query was selecting only the same details as the row you were looking at in the driving query. I replaced DUAL with another copy of the original table, and linked the two queries by the ID.
create table simple_test (ID number,ocdate date ,tc varchar2(2));
insert into simple_test values (1 ,to_date('9/26/2006','mm/dd/yyyy'),'CB');
insert into simple_test values (1 ,to_date('9/26/2006','mm/dd/yyyy'),'UT');
insert into simple_test values (2 ,to_date('9/26/2006','mm/dd/yyyy'),'CS');
insert into simple_test values (2 ,to_date('9/26/2006','mm/dd/yyyy'),'UT');
insert into simple_test values (3 ,to_date('9/26/2006','mm/dd/yyyy'),'CS');
insert into simple_test values (4 ,to_date('9/26/2006','mm/dd/yyyy'),'CB');
insert into simple_test values (5 ,to_date('9/26/2006','mm/dd/yyyy'),'CS');
insert into simple_test values (4 ,to_date('9/26/2006','mm/dd/yyyy'),'SS');
insert into simple_test values (5 ,to_date('9/26/2006','mm/dd/yyyy'),'SS');
insert into simple_test values (6 ,to_date('9/26/2006','mm/dd/yyyy'),'SS');
insert into simple_test values (7 ,to_date('9/26/2006','mm/dd/yyyy'),'SS');
select
a.id
from
simple_Test a
where
a.ocdate >= to_date('9/26/2006','mm/dd/yyyy') and
a.ocdate <= to_date('9/27/2007','mm/dd/yyyy') and
not exists
(select null
from simple_test b
where b.id = a.id
and b.ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and b.ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and b.tc not in ('CB', 'CS', 'UT')
);
D
----------------------
3
1
1
2
2
5 rows selected
|
|
|
Re: Query data rows [message #271613 is a reply to message #271607] |
Tue, 02 October 2007 03:35 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
I fail to see why you'd need dual. You also didn't specify why you're expecting that result. I assumed (dangerous, I know ) that you are looking for all id's that don't have a TC='SS' record. So, we can do this:
CREATE TABLE mytable (theid NUMBER, ocdate DATE, tc VARCHAR2(2))
/
INSERT INTO mytable VALUES (1, TO_DATE('9/26/2006','mm/dd/yyyy'), 'CB');
INSERT INTO mytable VALUES (1, TO_DATE('9/26/2006','mm/dd/yyyy'), 'UT');
INSERT INTO mytable VALUES (2, TO_DATE('9/26/2006','mm/dd/yyyy'), 'CS');
INSERT INTO mytable VALUES (2, TO_DATE('9/26/2006','mm/dd/yyyy'), 'UT');
INSERT INTO mytable VALUES (3, TO_DATE('9/26/2006','mm/dd/yyyy'), 'CS');
INSERT INTO mytable VALUES (4, TO_DATE('9/26/2006','mm/dd/yyyy'), 'CB');
INSERT INTO mytable VALUES (5, TO_DATE('9/26/2006','mm/dd/yyyy'), 'CS');
INSERT INTO mytable VALUES (4, TO_DATE('9/26/2006','mm/dd/yyyy'), 'SS');
INSERT INTO mytable VALUES (5, TO_DATE('9/26/2006','mm/dd/yyyy'), 'SS');
INSERT INTO mytable VALUES (6, TO_DATE('9/26/2006','mm/dd/yyyy'), 'SS');
INSERT INTO mytable VALUES (7, TO_DATE('9/26/2006','mm/dd/yyyy'), 'SS');
SELECT *
FROM mytable
WHERE theid NOT IN ( SELECT theid
FROM mytable
WHERE tc = 'SS'
)
/
DROP TABLE mytable PURGE
/ Run the script and see whether it's what you're looking for.
MHE
|
|
|
Re: Query data rows [message #271629 is a reply to message #271613] |
Tue, 02 October 2007 04:35 |
legna
Messages: 8 Registered: October 2007
|
Junior Member |
|
|
Hi,
Sorry about the extra trouble on creating the tables! I'll take note of this in the future.
I've tried
SELECT *
FROM mytable
WHERE theid NOT IN ( SELECT theid
FROM mytable
WHERE tc = 'SS'
)
initially but the query just hangs there.
The original table which i run this query is over 50,000 records and it locks the whole table for a long time. So i was hoping i can re-write the query to make it faster.
For
select
a.id
from
simple_Test a
where
a.ocdate >= to_date('9/26/2006','mm/dd/yyyy') and
a.ocdate <= to_date('9/27/2007','mm/dd/yyyy') and
not exists
(select null
from simple_test b
where b.id = a.id
and b.ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and b.ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and b.tc not in ('CB', 'CS', 'UT')
);
It works very much faster and gives the output that i want.
In this case, is using dual in the query inappropriate?
Thanks for both your help!
|
|
|
Re: Query data rows [message #271634 is a reply to message #271629] |
Tue, 02 October 2007 04:47 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Usiing dual is not inappropriate - it is wrong, in as much as it gives you the wrong answer.
Using Dual, all you were looking at in the NOT EXISTS subquery was the same row that you were currently processing in the main query. You needed to look at all the other rows in the table for the same id.
You could also tryselect a.id
from simple_test a
where a.ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and a.ocdate <= to_date('9/27/2007','mm/dd/yyyy')
minus
select b.id
from simple_test b
where b.ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and b.ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and b.tc not in ('CB', 'CS', 'UT');
This will give you a list of all the distinct id's that match your requirements, and should be faster.
When you time these queries, make sure that you are measuring the time it takes to return the whole result set, and not just the time it takes to getthe first results back
|
|
|
Re: Query data rows [message #271635 is a reply to message #271634] |
Tue, 02 October 2007 04:48 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
How do you mean 'Locks the whole table'?
Selects don't lock anything, and they don't block Inserts, Updates or Deletes.
|
|
|
Re: Query data rows [message #271639 is a reply to message #271635] |
Tue, 02 October 2007 05:07 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Furthermore 50,000 records isn't a lot. I posted a simplified example of how you could solve this. But agreed, you probably need to add the date columns to further restrict your result set. I would seriously consider the MINUS opion though.
MHE
[Updated on: Tue, 02 October 2007 05:08] Report message to a moderator
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 10 05:21:23 CST 2024
|