Solved

Problem with my query using WITH and FULL OUTER JOIN

Posted on 2013-01-18
10
355 Views
Last Modified: 2013-01-18
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

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)

Open in new window

0
Comment
Question by:Mark_Co
  • 6
  • 4
10 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
using WITH, you only need it once:

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'))
, 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)
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>FIRST_QUERY that are also a perfect match to SECOND_QUERY

By perfect match, do you mean ALL columns and ALL values?

An INTERSECT would probably be better/quicker:

select * from first_query
intersect
select * from second_query
0
 

Author Comment

by:Mark_Co
Comment Utility
ok great! But now I'm getting error ora-00904: "M" "ID": invalid identifier
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
I also noticed this is wrong:

FULL OUTER JOIN SECOND_QUERY ON (N.ID = M.ID)

You cannot reference N and M since those are part of the inner selects.

Make it:
FULL OUTER JOIN SECOND_QUERY ON (FIRST_QUERY.ID = SECOND_QUERY.ID)
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>ok great! But now I'm getting error ora-00904: "M" "ID": invalid identifier

lol... caught me typing!!!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Mark_Co
Comment Utility
using your first response ,i got the above error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>using your first response ,i got the above error

I corrected that error in http:#a38794421
0
 

Author Closing Comment

by:Mark_Co
Comment Utility
Thanks so much! :)
0
 

Author Comment

by:Mark_Co
Comment Utility
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now