Check that all rows match a given criterion

Question

requestId   Consultantid  statusid
1           2             10
2           2             10
3           2             10

I want to check if every row has a statusid of 10.

if (every row has a statusid of 10) then
  -----do this
endif;

Answer 1

I'm a bit rusty on PL-SQL, but something like this would work in T-SQL:

 if not exists (select * from your_table where statusid <> 10) then
    -- whatever
 end

Edit: Ok, apparantly in PL-SQL, you need to do something like this:

DECLARE
  notallten INTEGER;
BEGIN
  SELECT COUNT(*) INTO notallten 
  FROM your_table
  WHERE statusid <> 10
  AND ROWNUM = 1;
  IF notallten = 0 THEN
    -- Do something
  END IF;
END;

I don't have an Oracle server to test on though.

Answer 2

declare v_exists_status_10 number(1); ...

begin
...
-- v_exists_status_10 = 0 if no such row exist, 1 if at least one does
select count(*)
into   v_exists_status_10
from   dual
where  exists
  (select * from your_table where statusid <> 10);

if v_exists_status_10 > 0 then
...

Note that you could also do a dumb COUNT() into a variable, but it could be massively inefficient compared to EXISTS. With a COUNT() you'd have to scan all the records, whereas with an EXISTS as soon as it hits a statusid = 10 it can stop scanning.

Answer 3

Simpler solution, that accounts for NULLs in statusid:

for r in (
  select 1 dummy
  from   your_table
  where  (statusid != 10 or statusid is null)
  and    rownum = 1) loop
    -----do this
end loop;