Solved

Problem with my query using WITH and FULL OUTER JOIN

Posted on 2013-01-18
10
365 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 500 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 500 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 500 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

Industry Leaders: 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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

730 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