PL SQL multiple rows analysis

Question

I have two tables in Oracle, INVOICES and HOLDS, which I'm connecting using the invoice_id. Not all invoices will have holds, but some invoices will have multiple hold events. I want to select just one invoice and decode the release_status field. If there are multiple hold events, I need to analyse whether it's been released or not. If one line has no release code then the whole invoice is still held. Example table:

Invoice_num     |Hold_Reason    |Release_Lookup_code  

10001           |Inv Hold       |Quick release  
10002           |Inv Hold       |Quick release  
10003           |Inv Hold       |(NULL)  
10004           |Inv Hold       |Quick release  
10004           |Inv Hold       |Quick release  
10004           |Amt Hold       |(NULL)  
10005           |Inv Hold       |Variance Corrected  
10005           |Inv Hold       |Quick release  
10006           |  (NULL)       |(NULL)

The result I want to show:

Invoice num |Hold Reason    |Hold Status 

10001       |Inv Hold       |Released  
10002       |Inv Hold       |Released  
10003       |Inv Hold       |Held  
10004       |Inv Hold       |Held  
10005       |Inv Hold       |Released  
10006       |(NULL)         |Not Held

Currently I only have this code, which brings back multiple lines:

select
inv.invoice_num,
hold.hold_reason,
(CASE WHEN hold.invoice_id is not null                                              
then (CASE WHEN HOLD.RELEASE_LOOKUP_CODE is null then 'HELD' else 'RELEASED' END)   
else 'NOT HELD' END) Hold_Status
from AP.AP_INVOICES_ALL INV,
AP.AP_HOLDS_ALL HOLD
WHERE inv.invoice_id = hold.invoice_id (+)
order by 1

I thought of counting the number of invoices, but after that I still need to analyse the content of the Release_Lookup_Code field. I'm not looking for full answers, just some suggestions as to how I should proceed.

Does your hold table have a date/tiemstamp column? If not how do you know what order to evaluate multiple holds for one invoice in? — Alex Poole
– Alex Poole, Commented Nov 21, 2017 at 9:20
@Alex Poole If there are multiple holds, but all of them have been released, the invoice is 'Released'. If one hold has not been released, the whole invoice is 'Held' regardless when the hold event occured. I tried the MAX function as well, but it does not work as the date is irrelevant. — felpham
– felpham, Commented Nov 21, 2017 at 9:27
@ Tim Biegeleisen Sorry, my mistake, should be 'Amt Hold'. The table is not real data. — felpham
– felpham, Commented Nov 21, 2017 at 9:29
But how do you know which hold, if any, is current? Or can there only be one with a null release code for a given invoice? (And do you actually have a row in the hold table for 10006, or will no row exist if it's never been held? And is the it invoice_id or invoice_num, as your query uses both?) — Alex Poole
– Alex Poole, Commented Nov 21, 2017 at 9:30

Alex Poole · Accepted Answer · 2017-11-21 09:56:04Z

2

Similar kind of idea to @Arkadiusz's answer but using keep ... dense_rank instead of an explicit step to find a row number:

-- CTEs to provide your sample data
with ap_invoices_all (invoice_id) as (
  select 10001 from dual
  union all select 10002 from dual
  union all select 10003 from dual
  union all select 10004 from dual
  union all select 10005 from dual
  union all select 10006 from dual
), 
ap_holds_all (invoice_id, hold_reason, release_lookup_code) as (
  select 10001, 'Inv Hold', , 'Quick release' from dual
  union all select 10002, 'Inv Hold', 'Quick release' from dual
  union all select 10003, 'Inv Hold', null from dual
  union all select 10004, 'Inv Hold', 'Quick release' from dual
  union all select 10004, 'Inv Hold', 'Quick release' from dual
  union all select 10004, 'Amt Hold', null from dual
  union all select 10005, 'Inv Hold', 'Variance Corrected' from dual
  union all select 10005, 'Inv Hold', 'Quick release' from dual
  union all select 10006, null, null from dual
)
-- end of CTEs for sample data
select
  inv.invoice_id,
  max(hold.hold_reason) keep (dense_rank last order by case
      when hold.release_lookup_code is null then 1 else 0
    end) as hold_reason,
  case
    when max(hold.hold_reason) is null then 'NOT HELD'
    when max(hold.release_lookup_code)
      keep (dense_rank last order by case
          when hold.release_lookup_code is null then 1 else 0
        end) is not null then 'RELEASED'
    else 'HELD'
  end as hold_status
from ap_invoices_all inv
left join ap_holds_all hold
on hold.invoice_id = inv.invoice_id
group by inv.invoice_id
order by 1;

which gets:

INVOICE_ID HOLD_REA HOLD_STA
---------- -------- --------
     10001 Inv Hold RELEASED
     10002 Inv Hold RELEASED
     10003 Inv Hold HELD    
     10004 Amt Hold HELD    
     10005 Inv Hold RELEASED
     10006          NOT HELD

The ranking is to give priority to the hold records with null release codes, both to find the appropriate hold reason and to decide if there are any open holds - determining if it it is held or released.

With this version, if you have two open holds for an invoice then you'll only see it reported once, even if the hold reason is different (say if you has both amt and inv holds open for 10004). If you want to see both then you can group by both fields, which would simplify the rest of the query slightly; but you would also see released holds for both reasons too. You could also prioritise which hold reason you see in that scenario instead, if there is a preference.

answered Nov 21, 2017 at 9:56

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

3 Comments

felpham Over a year ago

Thanks Alex, much appreciated. I just want one line per invoice, so it's working fine. Similar issue to Arkadiusz's answer that I need it to be a bit more general as I don't know all invoice numbers.

Alex Poole Over a year ago

As I said on a comment on that answer, the invoice numbers are only here at all to create your sample data, in CTEs. You have the physical tables, we don't, so we're faking it. You only need the actual query after the -- end of CTEs comment.

felpham Over a year ago

Oh, sorry for the misunderstanding, I have tested your code now and it seems to be working fine. Really much appreciate your help!

Arkadiusz Łukasiewicz · Accepted Answer · 2017-11-21 09:31:43Z

2

Something like this. Do the duplication on invoice hold table.

   with AP_HOLDS_ALL(invoice_id, Hold_Reason, Release_Lookup_code) as
           (select 10001, 'Inv Hold', 'Quick release' from dual
            union all
            select 10002, 'Inv Hold', 'Quick release' from dual
            union all
            select 10003, 'Inv Hold', '' from dual
            union all
            select 10004, 'Inv Hold', 'Quick release' from dual
            union all
            select 10004, 'Inv Hold', 'Quick release' from dual
            union all
            select 10004, 'Amt Hold', '' from dual
            union all
            select 10005, 'Inv Hold', 'Variance Corrected' from dual
            union all
            select 10005, 'Inv Hold', 'Quick release' from dual
            union all
            select 10006, '', '' from dual)
       , AP_HOLDS_ALL_deplicated as
           (select s.*
                 , row_number()
                   over(partition by invoice_id
                        order by decode(Release_Lookup_code,  'Quick release', 1,  'Variance Corrected', 2,  3) desc)
                     rn
              from AP_HOLDS_ALL s)
       , AP_INVOICES_ALL(invoice_id) as
           (select 10001 from dual
            union all
            select 10002 from dual
            union all
            select 10003 from dual
            union all
            select 10004 from dual
            union all
            select 10005 from dual
            union all
            select 10006 from dual)
      select --inv.invoice_num,
             inv.invoice_id
           , hold.hold_reason
           , (case
                when hold.invoice_id is not null then
                  (case
                     when HOLD.RELEASE_LOOKUP_CODE is null then 'HELD'
                     else 'RELEASED'
                   end)
                else
                  'NOT HELD'
              end)
               Hold_Status
        from AP.AP_INVOICES_ALL INV, AP.AP_HOLDS_ALL_deplicated HOLD
       where inv.invoice_id = hold.invoice_id(+) and HOLD.rn = 1
    order by 1

answered Nov 21, 2017 at 9:31

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

4 Comments

felpham Over a year ago

Thanks for that. However, the table I showed is just an example, I don't know all the invoice numbers, I'm working with a database of hundreds of thousands of invoices. Can this be made more general?

Alex Poole Over a year ago

@felpham - the invoice IDs are only listed here to provide your sample data without creating physical tables; you won't use the two CTEs AP_HOLDS_ALL or AP_INVOICES_ALL as those are just representing your actual tables. You do need the AP_HOLDS_ALL_deplicated CTE though - though that could be made more generic, looking for any not-null value rather than specific release code.

Arkadiusz Łukasiewicz Over a year ago

with AP_HOLDS_ALL and AP_INVOICES_ALL are only statement to simulate real tables. Main part of answer is in AP_HOLDS_ALL_deplicated.

felpham Over a year ago

Sorry for the confusion @ArkadiuszŁukasiewicz, your help is much appreciated!

Collectives™ on Stack Overflow

PL SQL multiple rows analysis

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related