?
Solved

Problem with my query using WITH and FULL OUTER JOIN

Posted on 2013-01-18
10
Medium Priority
?
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 38794411
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 38794414
>>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
ID: 38794420
ok great! But now I'm getting error ora-00904: "M" "ID": invalid identifier
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38794421
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 77

Assisted Solution

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

lol... caught me typing!!!
0
 

Author Comment

by:Mark_Co
ID: 38794426
using your first response ,i got the above error
0
 
LVL 77

Expert Comment

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

I corrected that error in http:#a38794421
0
 

Author Closing Comment

by:Mark_Co
ID: 38794445
Thanks so much! :)
0
 

Author Comment

by:Mark_Co
ID: 38794455
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38794480
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

719 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