Rational Clearquest- Difference between two query

Question

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.

Answer 1

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!