Pl/sql dbms output

Question

I'm very new to pl/sql and I cannot make this query run.
I want it to find differences between two tables and then output ID of those transactions.
Any help would be appreciated!

SET SERVEROUTPUT ON
DECLARE
diff_id varchar2(50);
diff_id2 varchar2(50);

BEGIN
FOR dcount IN
SELECT
  O.transid ,
  ABB.transid
into diff_id, diff_id2 
FROM
  (SELECT *
  FROM O.transactions
  AND abdate >= trunc(sysdate -3)
  ) O
FULL OUTER JOIN
  (SELECT *
  FROM ABB.transactions
  AND  abdate >= trunc(sysdate -3)
  ) ABB
ON O.transid = ABB.transid

LOOP
 DBMS_OUTPUT.put_line (employee_rec.diff_id);
 DBMS_OUTPUT.put_line (employee_rec.diff_id2);  
END LOOP;
END;

Please add some sample data of both tables and what kind of differences you are talking about. Also add based on the sample data what would be the desired result. See [here how to proper ask a good sql question](https://meta.stackoverflow.com/a/271056/460557) — Jorge Campos, Jan 29 '18 at 23:52
There are 2 columns in each table, Id and date and my desired output would be id of transactions which are not in both tables. Ie 375 and 480. — Lostinsql, Jan 29 '18 at 23:55
Read the article I provide, specially the part **2. Give the Background - Relevant Data? Attempts?** — Jorge Campos, Jan 29 '18 at 23:58
Your PL/SQL is missing parentheses around the query. Also, run the query on its own (starting from `SELECT` until `ABB.transid`) and examine the results. — Jeffrey Kemp, Jan 30 '18 at 01:36

score 0 · Answer 1 · answered Jan 30 '18 at 07:20

my desired output would be id of transactions which are not in both tables. Ie 375 and 480

Ah, yes, 375 and 480. What about 832?

Anyway: you don't need PL/SQL to do that. Would SET operators do any good? For example, if you want to fetch ID s from the first table that aren't contained in the second one, you'd use

select id from first_table
minus
select id from second_table;

Both ways?

select 'in 1st, not in 2nd' what, id 
from (select id from first_table
      minus 
      select id from second_table)
union all
select 'in 2nd, not in 1st', id
from (select id from second_table
      minus
      select id from first_table);

Apply additional conditions, if necessary (ABDATE column, for example).

Pl/sql dbms output

1 Answers1