How to search for missing numbers in a table against a range of numbers?

I have a table with customer ids and I have a range of numbers I need to search against the table ids and when a number in the range isn't in the table output a message using an explicit cursor in a procedure.. The table ids are 1, 1, 1, 4, 4, 5, 5, 7, 8, 8 and the range of numbers are 1-8  How do I search through the table and range to get the missing numbers from the table, which are 2, 3, and 6 and print out the message in numerical order? I've been able to get 1, 2, 4, 5, 7, 6, 8. Please see the attached.
Attempt.txt
Barbara69Asked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
All you need to do to make it a procedure is to put the query into the procedure.  That is why I created the first response that way.  Combining everything into your procedure you get this:
--4-1 PROCEDURE

CREATE OR REPLACE PROCEDURE CUSTOMER_PURCHASES (p_low_custid IN NUMBER, p_high_custid IN NUMBER) 
AS 
BEGIN
    IF (p_low_custid <= p_high_custid) THEN
      FOR rec_purchase IN (select col1 from (SELECT distinct t.counter, CASE 
         WHEN counter = cust_id THEN counter 
                                     || ' has at least one order' 
         WHEN cust_id IS NULL THEN counter 
                                   || ' no purchase order' end col1 
FROM   purchase_order 
       right outer join (SELECT LEVEL + p_low_custid - 1 counter 
                         FROM   dual 
                         CONNECT BY LEVEL <= p_high_custid - p_low_custid + 
                                             1) t 
                     ON purchase_order.cust_id = t.counter) order by counter) LOOP  
        DBMS_OUTPUT.PUT_LINE(rec_purchase.col1);
      END LOOP;
      ELSE
      DBMS_OUTPUT.PUT_LINE('Invalid customer range');
      END IF;
END CUSTOMER_PURCHASES;

--4-1 ANONYMOUS BLOCK

DECLARE
lv_low_custid NUMBER(5) := 90001;
lv_high_custid NUMBER(5) := 90008;

BEGIN

CUSTOMER_PURCHASES(lv_low_custid, lv_high_custid);

END;

Open in new window

You were trying to keep track of everything with counters which is not necessary.  Let the query do the work.
0
 
slightwv (䄆 Netminder) Commented:
You can do this with straight SQL.  No need to procedural code
drop table tab1 purge;
create table tab1(cust_id number);

insert into tab1 values(1);
insert into tab1 values(1);
insert into tab1 values(1);
insert into tab1 values(4);
insert into tab1 values(4);
insert into tab1 values(5);
insert into tab1 values(5);
insert into tab1 values(7);
insert into tab1 values(8);
insert into tab1 values(8);
commit;


select
	case
		when counter=cust_id then counter || ' has at least one order'
		when cust_id is null then counter || ' no purchase order'
	end
from (
select counter, cust_id from
(select distinct cust_id from tab1)
right outer join
(select level counter from dual connect by level <=8) on counter=cust_id
)
order by counter
/

Open in new window


If you still want code, just copy/paste the select above and it is a simple cursor loop.  No need for the if/then and other loops.
0
 
johnsoneSenior Oracle DBACommented:
Not sure what you really need.  What you have attached doesn't match with what you are asking for.  Based on what you are asking for, which is find the missing numbers in the range, then this will certainly work:
SELECT           t.lvl missing_id 
FROM             purchase_order 
right outer join 
                 ( 
                        SELECT LEVEL lvl 
                        FROM   dual 
                               CONNECT BY LEVEL < 9) t 
ON               purchase_order.cust_id = t.lvl 
WHERE            purchase_order.cust_id IS NULL;ORDER BY cust_id;

Open in new window

If you are trying to find more information than that, we'll need some help.  You really don't need a procedure to find what you are looking for.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
johnsoneSenior Oracle DBACommented:
Apparently slightwv and I were typing at the same time...

Now, if you want to do what is in your procedure where a range is being specified, then you can do something like this.  This is done with SQL*Plus, so not all other tools will set up bind variables the same way.

First set up the variables:
variable lv_low_custid number;
variable lv_high_custid number;
begin
  :lv_low_custid := 90001;
  :lv_high_custid := 90008;
end;
/

Open in new window

Then the query would look like this:
SELECT t.lvl missing_id 
FROM   purchase_order 
       right outer join (SELECT LEVEL + :lv_low_custid - 1 lvl 
                         FROM   dual 
                         CONNECT BY LEVEL <= :lv_high_custid - :lv_low_custid + 
                                             1) t 
                     ON purchase_order.cust_id = t.lvl 
WHERE  purchase_order.cust_id IS NULL; 

Open in new window

If you want the list to show ids with orders as well, then you need to make it similar to what slightwv posted, the only changes should be in the select list.

Showing the code with bind variables rather than substitution variables because the bind variable version is what should be put into code.
0
 
slightwv (䄆 Netminder) Commented:
>>Apparently slightwv and I were typing at the same time...

Yes, we have no lives....  ;)
0
 
Barbara69Author Commented:
slightwv,
How do I get your code to work with cust_ids that have 9000 as the first 4 numbers of the ids?
0
 
johnsoneSenior Oracle DBACommented:
To modify, slightwv's query to use a hard coded value, just put the math into the subquery that generates the list of numbers.
SELECT CASE 
         WHEN counter = cust_id THEN counter 
                                     || ' has at least one order' 
         WHEN cust_id IS NULL THEN counter 
                                   || ' no purchase order' 
       END 
FROM   (SELECT counter, 
               cust_id 
        FROM   (SELECT DISTINCT cust_id 
                FROM   tab1) 
               right outer join (SELECT LEVEL + 90000 counter 
                                 FROM   dual 
                                 CONNECT BY LEVEL <= 8) 
                             ON counter = cust_id) 
ORDER  BY counter ;

Open in new window

Based on the original procedure you posted, it looked to my like you wanted the dynamic range, so I didn't give the hardcoded answer.
0
 
Barbara69Author Commented:
Is there a way that the code I submitted initially can be tweaked to get the correct results? When I run my code, I get all the correct ids with purchase and without purchase orders except 90003 is missing from the list and the results are out of order.
0
 
johnsoneSenior Oracle DBACommented:
What did you try?

I took your sample data, added 90000 to each id.  Then, I ran the query in the last post I made.  The results are:
CASEWHENCOUNTER=CUST_IDTHENCOUNTER||'HASATLEASTONEORDER'WHENCUS
---------------------------------------------------------------
90001 has at least one order
90002 no purchase order
90003 no purchase order
90004 has at least one order
90005 has at least one order
90006 no purchase order
90007 has at least one order
90008 has at least one order

8 rows selected.

Open in new window

That looks correct to me.
0
 
Barbara69Author Commented:
Hi johnsone,

I thought I had initially attached the file. Please see the attached.
Attempt.txt
0
 
johnsoneSenior Oracle DBACommented:
What you attached is a procedure.  There is no sample data and expected results.

You do not need a procedure to do what you are asking.  It is easily done in a single query.

From what I can see, solutions have been provided that should work.  If they are not working, then we need sample data and expected results so that we can change what has been posted so that it will work.
0
 
Barbara69Author Commented:
How to run the code for different ranges (i.e. the first time the range is 1-8 (2, 3, 6 no PO), the second time the range is 3-7 (3, 6 no PO)?
0
 
johnsoneSenior Oracle DBACommented:
You can either change the number being added to the level in post ID: 40744871, or use the method posted in ID: 40744179
0
 
Barbara69Author Commented:
johnsone, how do I get your code to work using sql developer.
0
 
Barbara69Author Commented:
johnsone, never mind, I copied to sql*plus and it works.
0
 
Barbara69Author Commented:
I know you say this can be done without a procedure, but I understand what I created and would like it if someone would tweak it to work the same as some of the other code that's been presented.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.