Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem with my query using WITH and FULL OUTER JOIN

Posted on 2013-01-18
10
Medium Priority
?
374 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 78

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 78

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 78

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 78

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 78

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 78

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

572 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