StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to abstract out subqueries?
text
Body
copied!<p>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:</p> <pre><code>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); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload