Solved

Problem with my query using WITH and FULL OUTER JOIN

Posted on 2013-01-18
10
364 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
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 76

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
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.

 
LVL 76

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 76

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 76

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 76

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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