December 01, 2020, 09:55:44 pm
News: IF YOU ARE A MEMBER FROM THE ORIGINAL SITE AND CAN'T LOGIN. POST IN THE FEEDBACK FORUM (AS GUEST) AND AN ADMIN WILL ASSIST YOU TO ACCESS THE SITE!
Pages: [1]   Go Down
Author Topic: Comparing Two Tables. Looking for missing records PL/SQL if possible  (Read 7067 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: January 09, 2008, 06:58:09 am »

Ok I've got two tables. Table one holds a bunch of information about a record and table two holds the raw record data (usually in a CLOB). The two tables have a common column called "internalid". When our software is operating correctly the raw record data is stored in table two and some information is parsed and stored in table one. For each "internalid" stored in table one should match a record in table two. We've recently found some problems where these records in table two are missing.

What I want to do is write an SQL statement that returns all the records in Table one where there is no matching record in Table two. I had this at one point
Code: (sql)
SELECT T1.INTERNALID, T2.INTERNALID
FROM Table1 T1
JOIN Table2 T2
ON T1.INTERNALID <> T2.INTERNALID;

Obviously this didn't work as a matter of fact because of the size of the tables I'm dealing with I locked up the server blush

Anywho if anyone can help me come up with a correct SQL that would be really ... well ... helpful.  jamie
Logged

-- signature --

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #1 on: January 09, 2008, 07:51:59 am »

While not perfect I did come up with this that at least outputs the INTERNALs of the missing records.
Code: (sql)
DECLARE
  v_fh UTL_FILE.file_type;
  v_ii NUMBER;
  v_last_ii NUMBER := 0;
  v_mi NVARCHAR2(19);
  v_msgcount NUMBER;
  v_fn NVARCHAR2(255);
  v_counter NUMBER := 0;
  v_output NVARCHAR2(255);
 
  CURSOR c_msgs IS
    SELECT INTERNALID, MIDENT
    FROM TABLE1;
   
   CURSOR c_msgcnt(p_id NUMBER) IS
    SELECT COUNT(INTERNALID) MSGCNT
    FROM TABLE2
    WHERE INTERNALID = p_id;
BEGIN
  v_fn := 'MISSING_RAW.txt';
  OPEN c_msgs;
  v_fh := utl_file.fopen('D:\', v_fn, 'w');
  LOOP
    FETCH c_msgs INTO v_ii, v_mi;
    EXIT WHEN c_msgs%NOTFOUND;
    IF v_ii <> v_last_ii THEN
      v_last_ii := v_ii;
      OPEN c_msgcnt(v_ii);
      FETCH c_msgcnt INTO v_msgcount;
      IF v_msgcount < 1 THEN
        v_counter := v_counter + 1;
        utl_file.put_line(v_fh, v_ii || ' ' || v_mi);
      END IF;
      CLOSE c_msgcnt;
    END IF;
  END LOOP;
  CLOSE c_msgs;
  v_output := '*** TOTAL MISSING: ' || v_counter || ' ***';
  utl_file.put_line(v_fh,v_output);
  utl_file.fclose(v_fh);
END;
/

It gives me a text file that has the internal id and part of the corresponding data needed for identification visually yay

Now I have to figure out why these records weren't entered properly.
Logged

-- signature --

Martin Wallace
Founders
*

Stars: +1/-0
Offline Offline

Posts: 3602


God is dead, I have surpassed him.


WWW
« Reply #2 on: January 10, 2008, 08:49:45 am »

SELECT T1.INTERNALID FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.INTERNALID = T2.INTERNALID WHERE T2.INTERNALID IS NULL;

Left Join ensures all records from T1 are reported.  Where there is no matching T2 record the returned value of T2.INTERNALID is null and hence you can filter and return only those instances.

Hope that helps.
Logged

Every time god kills an angel, I masturbate.

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #3 on: January 10, 2008, 08:54:25 am »

Well that's much more simplified Wink

Thanks Martin
Logged

-- signature --

Pages: [1]   Go Up
Print
 
Jump to: