How to find records where specific values are different I have two conditions:


first has a value of Null
second has a value of XX


In the database there should Not be one without the other and the Rates should match.

I want to find all records where the Rates are different for the Null and XX records.

How can I do this in PL/SQL?

The database is a work db of procedure codes and I'm looking for a query that will find all procedure codes that have a modifier = null and modifier = xx where the rates are not equal. All fields are within the same table.

I'm thinking a nested type query but can't seem to figure it out.

Table name Procedure Code Price

Procedure Code = T2015

Modifier = Null.  With a Rate of $32.50

Modifier = XX.    With a Rate of $23.70

The table Procedure Code Price holds many CPT &amp; HCPS codes. Each code can have multiple lines with different modifiers and rates. The correct entry for the example above would be the rates for Null &amp; XX modifier are the same. What I want to find are the entries where the rates are different for these two modifiers.
In principle, the solution should probably be coded as a single SQL statement.  However, you could also use PL/SQL and a CURSOR FOR LOOP.  The question text does not give us any test data.  Please let us know if the following solution needs major adjustments (Oracle version: 12c).

create table a_test(
  first number
, second varchar2(64)
, rates number
);

-- insert some test data
-- first column: range 1-100, every third row: NULL
-- second column: all XX
-- third column: values between 0 and 9
begin
  for i in 1 .. 100
  loop
    insert into a_test values (i, 'XX', mod(i,10));
  end loop;
  update a_test 
  set first = null
  where mod(first,3) = 0;
end;


The test table contains 100 rows. Use SQL first ...

-- query1: only the rows where first IS NULL -&gt; 33 rows
select count(*)
from a_test
where first is null
  and second = 'XX'

-- query2: no duplicates for rates -&gt; 10 rows
select first, second, rates
from a_test
where first is null
  and second = 'XX'
group by rates, first, second
order by rates;


When you are using PL/SQL, there are various ways of coding this.  Maybe one of the following will be helpful in your situation:

-- query2 in a cursor for loop
begin
  for rec in (
    select first, second, rates from a_test
    where first is null
      and second = 'XX'
    group by rates, first, second
    order by rates )
  loop
     dbms_output.put_line ( 
       rec.first || ' ' || rec.second || ' ' || rec.rates  );
   end loop;
end;

-- IF in a cursor for loop (query2 without a WHERE clause)
begin
  for rec in ( 
    select * from a_test
    group by rates, first, second
    order by rates )
  loop
     if rec.first is null and rec.second = 'XX' then
       dbms_output.put_line ( 
         'null' || ' ' || rec.second || ' ' || rec.rates  );   
       -- alternative: code rec.first instead of 'null'  
     end if;
   end loop;
end;