Problem with my query using WITH and FULL OUTER JOIN
I am trying to have rows return from a SQL that is probably similar to this. The goal is that I need to know if any rows are returned from FIRST_QUERY that are also a perfect match to SECOND_QUERY. If rows are returned, then I'll know I have a data discrepancy because ideally NOTHING should be returned, which means that all the data matches, I'm pretty sure that's what I want. Otherwise I may have that backwards. In any case, whether all data comes back or no data comes back, that should tell me everything matches up and there are no discrepancies, I would imagine.
The error I keep getting is ORA-00928: missing SELECT keyword. My first impression was that I need to wrap both WITH statements inside of a wrapper like SELECT * FROM (fill in the two WITH statements here) but that also didn't work
The error I keep getting is ORA-00928: missing SELECT keyword. My first impression was that I need to wrap both WITH statements inside of a wrapper like SELECT * FROM (fill in the two WITH statements here) but that also didn't work
WITH FIRST_QUERY AS
(SELECT M.ID
FROM TABLE1 M
INNER JOIN TABLE2 PER
ON (PER.ID = M.ID)
WHERE M.COMP IN ('ACCOMP1'
,'ORG2'))
WITH SECOND_QUERY AS
(SELECT N.ID
FROM TABLE3 N
WHERE TYPE IN ('102'
,'103')
AND SEQ = '1' --current row of data, if desired
AND STATUS = 'A' --active ('I' = inactive), if desired
)
SELECT *
FROM FIRST_QUERY
FULL OUTER JOIN SECOND_QUERY
ON (N.ID = M.ID)SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
using your first response ,i got the above error
ASKER
Thanks so much! :)
ASKER
Another question though, if ya don't mind: I got results back, many hundreds actually. Would that indicate to me that those hundreds of returned rows are perfect matches? Is that what that means?
It depends on what you call a 'perfect match'.
Since you join on the ID column, the id's should match in both queries.
When troubleshooting things like this I tend to use the brute force approach:
I spool out the first set of results, then spool out the second set of results, then manually verify your results to see if rows should or should not return.
Since you join on the ID column, the id's should match in both queries.
When troubleshooting things like this I tend to use the brute force approach:
I spool out the first set of results, then spool out the second set of results, then manually verify your results to see if rows should or should not return.
ASKER