CODEWITHSUNDEEP
sqloracle-databaseplsql
Jimenemex
Jimenemex

Reputation: 3176

Comparing a column to a set of numbers

I have to compare a column in a database to a set of numbers. Essentially, I want to output all the numbers that are in the set, but not in the table.

I've tried this, but I still get the columns that are in the set and are not in the table.

This is what I tried so far,

DECLARE
  CURSOR c_applnbr IS
    SELECT * FROM appl a
    WHERE a.applnbr IN 
    (7701, 7702, 7703, 7704, 7705, 7706, 7707, 7708, 7709, 7710, 7711,
    7712, 7713, 7714, 7715, 7716, 7717, 7718, 7719, 7720, 7721, 7722, 
    7723, 7724, 7725, 7727, 7728, 7729, 7730, 7731, 7732, 7733, 7734, 
    7735, 7736, 7737, 7738, 7739, 7740, 7741, 7742, 7743, 7744, 7745,
    7746, 7747, 7748, 7749, 7750, 7751, 7752, 7753, 7754, 7755, 7756,
    7757, 7758, 7759, 7760, 7761, 7762, 7763, 7764, 7765, 7766, 7767,
    7768, 7769, 7770, 7771, 7772, 7773, 7774, 7775, 7776, 7777, 7778, 
    7779, 7781, 7782, 7783, 7784, 7786, 7787, 7788, 7789, 7791, 7792,
    7798, 7799, 7801, 7802, 7803, 7804, 7805, 7818, 7819, 7833); -- There is no 7833 in the table
  CURSOR c_applnbr_total IS SELECT * FROM appl;

  TYPE t_applnbr IS TABLE OF appl.applnbr%TYPE;

  applnbrs t_applnbr := t_applnbr();
  applnbrs_total t_applnbr := t_applnbr();
  commonNumbers t_applnbr := t_applnbr();

  counter integer := 0;
  counter2 integer := 0;


BEGIN
  -- Fill applnbrs
  FOR n IN c_applnbr LOOP
    counter := counter + 1;
    applnbrs.EXTEND;
    applnbrs(counter) := n.applnbr;
  END LOOP;
  -- Fill applnbrs_total
  FOR n IN c_applnbr_total LOOP
    counter2 := counter2 + 1;
    applnbrs_total.EXTEND;
    applnbrs_total(counter2) := n.applnbr;
  END LOOP;
  -- Getting the numbers in the set, and not in the table.
  commonNumbers := applnbrs MULTISET EXCEPT DISTINCT applnbrs_total;
  -- Checking counts
  dbms_output.put_line('applnbrs count: ' || applnbrs.COUNT); -- Holds 97
  dbms_output.put_line('applnbrs_total count: ' || applnbrs_TOTAL.COUNT); -- Holds 1979
  dbms_output.put_line('commonNumbers.count: ' || commonNumbers.COUNT); -- Holds 0

  FOR n IN 1..commonNumbers.COUNT LOOP -- Doesn't go in loop
    dbms_output.put_line(n || ': ' || commonNumbers(n));        
  END LOOP;

END;

I have't done Pl/SQL in a while, so maybe I'm not using nested tables correctly.

There is no entry for 7833 in the table. So the desired output should spit out 7833, but commonNumbers.COUNT = 0. So essentially everything matches.

Upvotes: 0

Views: 67

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

You can use the XMLTABLE function and NOT EXISTS in simple select.There is no need of PL/SQL.

SELECT * FROM 
(
SELECT
    to_number(column_value) AS appl
FROM
    XMLTABLE ( '7701, 7702, 7703, 7704, 7705, 7706, 7707, 7708, 7709, 7710, 7711,
    7712, 7713, 7714, 7715, 7716, 7717, 7718, 7719, 7720, 7721, 7722, 
    7723, 7724, 7725, 7727, 7728, 7729, 7730, 7731, 7732, 7733, 7734, 
    7735, 7736, 7737, 7738, 7739, 7740, 7741, 7742, 7743, 7744, 7745,
    7746, 7747, 7748, 7749, 7750, 7751, 7752, 7753, 7754, 7755, 7756,
    7757, 7758, 7759, 7760, 7761, 7762, 7763, 7764, 7765, 7766, 7767,
    7768, 7769, 7770, 7771, 7772, 7773, 7774, 7775, 7776, 7777, 7778, 
    7779, 7781, 7782, 7783, 7784, 7786, 7787, 7788, 7789, 7791, 7792,
    7798, 7799, 7801, 7802, 7803, 7804, 7805, 7818, 7819, 7833'
) ) t  WHERE
NOT EXISTS (
    SELECT
        1
    FROM
        applnbr a
    WHERE
        a.appl = t.appl
);

Note: XMLTABLE works only for Numbers.

Upvotes: 1

trincot
trincot

Reputation: 351228

A solution would be to use a query that produces the needed values. Something like:

select 7701 as applnbr from dual union all
select 7702 from dual union all
select 7703 from dual union all
select 7704 from dual union all
select 7705 from dual

... etc. As this becomes quite long, and a big part of your numbers are consecutive, you could also compress this a bit with a recursive query:

select applnbr 
from   (select 7700 + level as applnbr from dual connect by level < 134)
where  applnbr not in (7726, 7780, 7785, 7790, 7800)
and    applnbr not between 7793 and 7797
and    applnbr not between 7806 and 7817
and    applnbr not between 7820 and 7832

This produces all numbers between 7701 and 7833 and then removes some of them via the where clause to get to the actual set.

Finally, you can use an outer join to get the desired result:

with needed as (
     select applnbr 
     from   (select 7700 + level as applnbr from dual connect by level < 134)
     where  applnbr not in (7726, 7780, 7785, 7790, 7800)
     and    applnbr not between 7793 and 7797
     and    applnbr not between 7806 and 7817
     and    applnbr not between 7820 and 7832
)
select    needed.applnbr 
from      needed
left join appl
       on appl.applnbr = needed.applnbr
where     appl.applnbr is null

Upvotes: 1

Related Questions