Eliminate duplicate code snippets in PL/SQL function

Question

I need help/advice on optimizing my function. How can I avoid duplicating this fragment of code:

    select 1 from order_attr oa
    where oa.order_id = o.order_id and ea.order_attr_name = 'administrationId'

My PL/SQL function below (this is an example, in real code the fragment is repeated many times):

   begin
   select count (distinct o.order_id)
   into :result
   from orders o
   where o.ext_order_id = :ExtOrderId and o.service_id not like 'tech#%' and (:Include = 1 and exists
   (
      select 1 from order_attr oa
      where oa.order_id = o.order_id and ea.order_attr_name = 'administrationId'  
   )
   or :Include = 0 and not exists
   (
      select 1 from order_attr oa
      where oa.order_id = o.order_id and ea.order_attr_name = 'administrationId' 
   ));
   exception
   when others then
      :result := 0;
   end;

You could try to put it in the cte

BI Dude
– BI Dude

2021-07-09 06:30:36 +00:00
Commented Jul 9, 2021 at 6:30 — BI Dude
– BI Dude, Commented Jul 9, 2021 at 6:30

ekochergin · Accepted Answer · 2021-07-09 06:30:50Z

1

the left join is what will help you here

 from orders o
 left join order_attr oa 
   on oa.order_id = o.order_id and ea.order_attr_name = 'administrationId'  
where o.ext_order_id = :ExtOrderId and o.service_id not like 'tech#%' 
  and (:Include = 1 and oa.order_id is not null -- exists
   or :Include = 0 and oa.order_is is null; -- not exists

I don't have data to test, but I hope you'll get the idea

answered Jul 9, 2021 at 6:30

ekochergin

4,1592 gold badges14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2021-07-09 06:56:15Z

Here is a solution that you may or may not like :-)

First of all, the order_id should be the unique identifier of an order. So I'd change count(distinct o.order_id) to count(*), because there are no nulls to skip; we just want to count order rows.

About the same goes for order attributes; order_attr seems to be a key/value table, where for some reason orders can be so very different from each other that they need different columns, which is why you have this table. There must not be two rows for the same attribute in an order. Having said this, you can just count how many administration IDs the order has, and it will be 0 or 1.

begin
   select count(*))
   into :result
   from orders o
   where o.ext_order_id = :ExtOrderId and o.service_id not like 'tech#%' 
   and :Include = 
   (
      select count(*) from order_attr oa
      where oa.order_id = o.order_id and ea.order_attr_name = 'administrationId'  
   );
   exception
   when others then
      :result := 0;
   end;

If I am wrong and for some reason you can have multiple administration IDs per order, then just add and rownum = 1 to the subquery in order to still end up wth a count of 0 or 1.

Collectives™ on Stack Overflow

Eliminate duplicate code snippets in PL/SQL function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related