0

I'm trying to use Except keyword to filter uncommon records between two query output by changing the query (designed in query designer) in sql editor pane. I'm aware that if you change a query designed in query designer using sql editor pane, it then again cannot be modified in query designer. All I need is that I have query 1 output and query 2 output and I want to find only records that are not there in query 2 output ( query 1 minus/except query2). Any help would be much appreciated. Thanks.

Sneftel
  • 40,271
  • 12
  • 71
  • 104
user2705120
  • 249
  • 5
  • 12
  • Sounds like my situation with actuate. If it were me, I'd write my own sql and do without the query designer. – Dan Bracuk Dec 12 '13 at 16:56
  • Thanks for your quick trunaround. The problem for me, i do not have acceess to connect the database through sql;but i do have access to change query in sql editor pane and save. It actually does not support EXCEPT/MINUS..it says that there is a syntax error nearby Except keyword...I could do only OR/AND between two queries..but i want to fetch uncommon records.. – user2705120 Dec 12 '13 at 16:59

1 Answers1

0

There are going to be a multitude of ways to solve this problem. If you already have the two scripts, then I think the easiest way is below.

You can put Query 1 and 2 into their own CTEs and then join them together on a common ID where the Query 2 ID is null, like so:

With Query_1_CTE as (
--Insert Query 1 script here
)

, Query_2_CTE as (
--Insert Query 2 script here
)

Select --list out query 1 fields here

From Query_1_CTE Q1
    Left Outer Join Query_2_CTE Q2
        on Q1.ID = Q2.ID

Where Q2.ID is null

That will return only records present in Query 1 but no Query 2. Hope this helps!

Christopher Brown
  • 2,170
  • 1
  • 13
  • 19