Need Query Help 2

[dpgirl]

I’m new to Oracle SQL but have worked in MS SQL Server before. I need help converting my query.

I have 2 tables:


Table A

CallDate CallID Segnum
1/1/2005 12:04:50 AM 123 1
1/1/2005 12:34:12 AM 123 2
1/1/2005 12:34:12 AM 123 3
1/2/2005 08:42:15 PM 234 1
1/2/2005 08:55:10 PM 234 2
5/2/2005 02:35:16 PM 123 2



Table B

CallDate CallID Segnum
1/1/2005 123 1
1/1/2005 123 3
1/2/2005 234 1
1/2/2005 234 2


I want to pull all records in Table A where both the CallDate and CallID values exist in Table B but the Segnum value doesn’t match. (I want to ignore the time stamp in Table A when comparing date values to Table B.)

So the result I would get from the above example would be one record:

CallDate CallID Segnum
1/1/2005 12:34:12 AM 123 2


My SQL Server query would be something like this?

Select *
from TableA
where segnum not in
(select segnum
from TableB
where TableB.CallID = TableA.CallID
and TableB.CallDate = formatdate(TableA.CallDate,"mm/dd/yyyy")

 

[Thargy]

dpgirl,

I have the following for you.

CREATE TABLE TABLE_A
(
CALL_DATE DATE,
CALL_ID   INTEGER,
SEGNUM    INTEGER
);

CREATE TABLE TABLE_B AS SELECT * FROM TABLE_A;

INSERT INTO TABLE_A VALUES(SYSDATE,123,1);
INSERT INTO TABLE_A VALUES(SYSDATE,123,2);
INSERT INTO TABLE_A VALUES(SYSDATE,123,3);
INSERT INTO TABLE_A VALUES(SYSDATE+1,234,1);
INSERT INTO TABLE_A VALUES(SYSDATE+1,234,2);
INSERT INTO TABLE_A VALUES(SYSDATE+1,123,2);
COMMIT;

INSERT INTO TABLE_B VALUES (TRUNC(SYSDATE),123,1);
INSERT INTO TABLE_B VALUES (TRUNC(SYSDATE),123,3);
INSERT INTO TABLE_B VALUES (TRUNC(SYSDATE+1),234,1);
INSERT INTO TABLE_B VALUES (TRUNC(SYSDATE+1),234,2);
COMMIT;

SELECT *
FROM Table_A
WHERE NOT EXISTS
(SELECT 1
FROM Table_B
WHERE Table_B.Call_ID = Table_A.Call_ID
AND Table_B.Call_Date = TRUNC(Table_A.Call_Date));

When run from sqlplus this produces the following log file

SQL> @dpgirl

Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.


CALL_DATE              CALL_ID     SEGNUM                                       
------------------- ---------- ----------                                       
2006-05-19 22:05:00        123          2                                       

SQL> spool off

I used sysdate for convenience when creating dates, as I am in the U.K. and wasn't sure if you are stateside, and therefore may be using different date conventions.

Notice that the sysdate includes a time, and that to get rid of it one uses the TRUNC function.

I changed your NOT IN clause to a NOT EXISTS, as often it's quicker with large data volumes.

I hope this helps with your problem.

Regards

Tharg

Grinding away at things Oracular

 

[Thargy]

dpgirl,

oops, I forgot to say that since I'm at home, I can only do this on 10g, and this is a version 8 forum. My mistake.
However, since this is vanilla DDL/DML, it should work.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

Frankly, DPGirl, I'll bet that your SQL Server query would first, fail (since you are missing a closing paren at the end of your code), then second, return more data than you want.

I'll illustrate [by using (working) equivalent Oracle code] why it is that you get more data than you want (given the data you provided):

Select * 
from TableA
where segnum not in 
(select segnum
from TableB
where TableB.CallID = TableA.CallID
  and TableB.CallDate = trunc(TableA.CallDate));

CALLDATE      CALLID     SEGNUM
--------- ---------- ----------
01-JAN-05        123          2
02-MAY-05        123          2

The first row is correct, so I won't bother to explain its existence. For the second row, however, when it appears in TableA, contrary to your wishes, it is part of the result set because its SEGNUM (2) likewise does not appear in the "NOT IN" result set since the "NOT IN" subquery produces a NULL set of values, since there is no matching CALLDATE between the tables, thus it incorrectly appears in the result set. This would be the case for both Oracle and for SQL Server (given your SQL Server code).

Now, following is code that achieves your result set:

select distinct a.*
  from tablea a, tableb b
 where trunc(a.calldate)=trunc(b.calldate)
   and a.callid = b.callid
   and not exists
       (select 'x' from tableb c
         where a.segnum = c.segnum
           and trunc(a.calldate)=trunc(c.calldate)
           and a.callid = c.callid
           and a.segnum = c.segnum
        );

CALLDATE      CALLID     SEGNUM
--------- ---------- ----------
01-JAN-05        123          2

The "EXISTS" and its converse, "NOT EXISTS" may seem foreign to the uninitiated, but it is extremely useful. Basically, it checks to see if anything at all resulted from the subquery, which, in your case, looks for matches between the two tables; the above code basically says print out rows "WHERE matches don't exist between the two tables", which is what you wanted.

Let us know if this tactic works for your real data set.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Sorry, Tharg, that I posted similar code to yours subsequent to yours (I was called away from my desk in the middle of my post and didn't refresh, before actually posting, to see if anyone had yet responded.)

I prefer the simplicity of your code to my code, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[dpgirl]

Thank you Thargtheslayer and SantaMufasa!

You are correct SantaMufasa that my original query produced more than my desired result. Your suggested code worked perfectly!

Out of curiosity, how do you create the Code box that's imbedded in your response?

 

[SantaMufasa]

At the bottom of any "Reply" box (where you type in responses to postings), there are 3 check-box links: "E-Mail Notification", "Emoticons/Smileys", and "Process TGML". Click on "Process TGML" to see the coding that you can embed in your replies. Drag the scroll bar down toward the bottom of that link (to where the topic is "CODE")...that explains how we create CODE boxes, and all of the other topics will be of interest to you, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]