0

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 & 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 & XX modifier are the same. What I want to find are the entries where the rates are different for these two modifiers.

closed as unclear what you're asking by Evan Carroll, mustaccio, McNets, Marco, Lennart Jun 6 '17 at 7:56

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

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 -> 33 rows
select count(*)
from a_test
where first is null
  and second = 'XX'

-- query2: no duplicates for rates -> 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;

Not the answer you're looking for? Browse other questions tagged or ask your own question.