Apache Phoenix: Using MINUS throws an error

My first step would be to rank the service_instruction by specificity/generality - I don't think you have provided enough information to be sure what you are after but assuming that it is based on the number of 1s and ties are broken by: bank_num is more specific than loan_type_cd is more specific than client_num:

select *, row_number() over ( order by case when bank_num=1 then 1 else 0 end+
                                       case when loan_type_cd=1 then 1 else 0 end+
                                       case when client_num=1 then 1 else 0 end,
                                     case when bank_num=1 then 1 else 0 end,
                                     case when loan_type_cd=1 then 1 else 0 end,
                                     case when client_num=1 then 1 else 0 end ) as gen
from service_instruction;

produces:

instruction_num service_num instruction_desc    bank_num    region_num  loan_type_cd    client_num  gen
3   251 Take the photos, yeah   17  96  3   1   1
4   251 Bid for debris removal. 1   471 1   7   2
7   251 Bid for debris removal. Do not perform  1   3   1   1   3

This can then be joined to order and work_order_line and filtered for the row for each work_order_line_num with the lowest generality (gen), perhaps something like this:

with w as (
    select *, row_number() over ( order by case when bank_num=1 then 1 else 0 end+
                                           case when loan_type_cd=1 then 1 else 0 end+
                                           case when client_num=1 then 1 else 0 end,
                                         case when bank_num=1 then 1 else 0 end,
                                         case when loan_type_cd=1 then 1 else 0 end,
                                         case when client_num=1 then 1 else 0 end ) as gen
    from service_instruction )
select *
from( select l.*, instruction_desc, row_number() over ( partition by l.work_order_line_num, 
                                                                     l.order_num, 
                                                                     l.service_num
                                                        order by gen ) as gen_rank
      from work_order_line l join [order] o on(o.order_num=l.order_num)
           join w on( l.service_num=w.service_num 
                      and (o.bank_num=w.bank_num or w.bank_num=1) 
                      and (o.loan_type_cd=w.loan_type_cd or w.loan_type_cd=1)
                      and (o.client_num=w.client_num or w.client_num=1)) ) z
where gen_rank=1

which produces:

work_order_line_num order_num   service_num instruction_desc    gen_rank
20  1   251 Bid for debris removal. Do not perform  1
21  2   251 Bid for debris removal. 1
22  3   251 Take the photos, yeah   1
26  4   251 Bid for debris removal. Do not perform  1

notes:

1. I've assumed you need to partition by work_order_line_num, order_num and service_num but maybe not all are necessary depending on the PK of work_order_line
2. I've ignored the pricing_region complexity you mention but don't specify completely - hopefully you'll be able to work it into the query in a similar manner to the rest
3. You'll probably have to fix the CTE with your rules for specificity/generality
4. I tested on 2008R2 - YMMV if you are on an earlier version

This question is somewhat old, so you may have already figured out your problem, but if I may offer an alternative...

It may be better to use a MERGE statement in this situation (click the link for the publib entry):

MERGE INTO mySchema.myTable tab USING (
        VALUES ('abc', '123')
    ) AS merge (C1, C2)
    ON  tab.column1 = merge.C1
    AND tab.column2 = merge.C2
    WHEN MATCHED THEN
        IGNORE
    WHEN NOT MATCHED THEN 
        INSERT (column1, column2)
        VALUES (merge.C1, merge.C2)

This will take the columns in the "fake" table merge, and compare them using the keys in the ON clause, and if there is not a match, then it will use the INSERT statement. If there is a match, that row will just be ignored.

However, the availability of MERGE depends on your platform. I'm fairly certain MERGE has been in Linux/Unix/Windows DB2 since v8 (although, you can only use PREPARE'd merges since 9.7), and it was added in z/OS DB2 in v9.1. I don't know about the other platforms (AS/400, etc.).