Barbara69
asked on
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
Attempt.txt
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;
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.
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:
Showing the code with bind variables rather than substitution variables because the bind variable version is what should be put into code.
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;
/
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;
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.
>>Apparently slightwv and I were typing at the same time...
Yes, we have no lives.... ;)
Yes, we have no lives.... ;)
ASKER
slightwv,
How do I get your code to work with cust_ids that have 9000 as the first 4 numbers of the ids?
How do I get your code to work with cust_ids that have 9000 as the first 4 numbers of the ids?
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 ;
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.
ASKER
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.
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:
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.
That looks correct to me.
ASKER
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.
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.
ASKER
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)?
You can either change the number being added to the level in post ID: 40744871, or use the method posted in ID: 40744179
ASKER
johnsone, how do I get your code to work using sql developer.
ASKER
johnsone, never mind, I copied to sql*plus and it works.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.