How to abstract out subqueries?

Question

I have a query that needs to check that all fields have values are in a list of valid codes. Right now I'm calling the same subquery over and over and over again. I want to abstract the subquery out so that it is faster and the code isn't repeated. This is the query in question:

select count(*)
into cnt
from pdv_validcodes c
where c.code_type = 'YNNA'
 and (upper(:new.spec_1) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_1 is null)
 and (upper(:new.spec_2) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_2 is null)
 and (upper(:new.spec_3) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_3 is null)
 and (upper(:new.spec_4) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_4 is null)
 and (upper(:new.spec_5) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_5 is null)
 and (upper(:new.spec_6) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_6 is null)
 and (upper(:new.spec_7) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_7 is null)
 and (upper(:new.spec_8) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_8 is null)
 and (upper(:new.spec_9) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_9 is null)
 and (upper(:new.spec_10) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.spec_10 is null)
 and (upper(:new.add_spec_1) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_1 is null)
 and (upper(:new.add_spec_2) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_2 is null)
 and (upper(:new.add_spec_3) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_3 is null)
 and (upper(:new.add_spec_4) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_4 is null)
 and (upper(:new.add_spec_5) in
     (select code from pdv_validcodes where code_type = 'YNNA') or
     :new.add_spec_5 is null);

Answer 1

Michael,

I haven't had a chance to test this but as it's trigger code and therefore PL/SQL, something along the lines of this might work:

CREATE OR REPLACE TYPE "strarray" AS TABLE OF VARCHAR2 (255)
/


DECLARE
   validcodes strarray;
BEGIN
   SELECT code 
     BULK COLLECT INTO validcodes
     FROM pdv_validcodes
    WHERE code_type = 'YNNA'
   UNION
   SELECT 'NULL'
     FROM dual;

   IF  NVL(upper(:new.spec_1), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_2), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_3), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_4), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_5), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_6), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_7), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_8), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_9), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.spec_10), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_1), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_2), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_3), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_4), 'NULL') MEMBER OF validcodes
   AND NVL(upper(:new.add_spec_5), 'NULL') MEMBER OF validcodes
   THEN
      -- Business logic
   ELSE
      -- Business logic
   END IF;
END;

Answer 2

Depending on the Oracle version, you could use the WITH clause to factor out subqueries. I'm not sure that buys you too much in this case, but

with valid as (
  select code
    from pdv_validcodes
   where code_type = 'YNNA' )
select count(*)
into cnt
from pdv_validcodes c
where c.code_type = 'YNNA'
 and (upper(:new.spec_1) in
     (select * from valid) or
     :new.spec_1 is null)
 and (upper(:new.spec_2) in
     (select * from valid) or
     :new.spec_2 is null)
 and (upper(:new.spec_3) in
     (select * from valid) or
     :new.spec_3 is null)
...