Home » SQL & PL/SQL » SQL & PL/SQL » Query data rows
Query data rows [message #271607] Tue, 02 October 2007 03:13 Go to next message
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

ID

1
1
2
2
3


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:
ID

1
1
2
2
3
4
5


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Wink) 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 try
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') 
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Query data rows [message #271643 is a reply to message #271607] Tue, 02 October 2007 05:34 Go to previous messageGo to next message
legna
Messages: 8
Registered: October 2007
Junior Member
Point understood.

Thanks alot for your time and effort!
Re: Query data rows [message #271773 is a reply to message #271607] Tue, 02 October 2007 20:16 Go to previous messageGo to next message
legna
Messages: 8
Registered: October 2007
Junior Member
Just to clarify, if MINUS returns distinct id, will it result in a SORT first?
Re: Query data rows [message #271802 is a reply to message #271773] Wed, 03 October 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Distinct does not require sort.
Does this answer your question?

Regards
Michel
Re: Query data rows [message #271866 is a reply to message #271607] Wed, 03 October 2007 03:51 Go to previous message
legna
Messages: 8
Registered: October 2007
Junior Member
yes!

Thanks!
Previous Topic: when do apply last dml ?
Next Topic: delete issue
Goto Forum:
  


Current Time: Sun Nov 10 05:21:23 CST 2024