Link to home
Start Free TrialLog in
Avatar of edoc_admin
edoc_admin

asked on

Oracle - Joined tables using Analytical Function

Hello,

I have a query that uses Analytical Functions and I need help on joining with a Source table in order to get the Description of the Type and Status fields.

This is the query:
select i.Document_ID,
       i.Scan_Date,
       i.Invoice_Number,
       i.Invoice_Date,
       (select max(n.Task_Status) keep (dense_rank last order by Timestamp)
               from Invoice_Activities  n
              where n.Invoice_ID = i.ID) Inv_Status,
       (select max(n.Task_Type) keep (dense_rank last order by Timestamp)
               from Invoice_Activities  n
              where n.Invoice_ID = i.ID) Inv_Type
  from Invoice  i
 where i.Scan_Date = TO_DATE('2014-08-04', 'YYYY-MM-DD')
 order by i.Invoice_Number;

Results:
DOCUMENT_ID   SCAN_DATE   INVOICE_NUMBER   INVOICE_DATE   INV_STATUS   INV_TYPE
11967804             8/4/2014       003060547                 7/30/2014          29                     28
11967527             8/4/2014       003060548                 7/30/2014          29                     26
11967503             8/4/2014       003060549                 7/30/2014          29                     26
...
11967526             8/4/2014       003060550                 7/30/2014          29                     26

From the above query, I'd like to display the DESCRIPTION of the INV_STATUS and INV_TYPE instead of the cryptic numbers 26, 28, 29... In order to do that, I'd have to join INVOICE_ACTIVITIES with another table called CODE (The DESCRIPTION field is in the CODE table) i.e.

select c1.Description, c3.Description
 from Invoice_Activities  ia,
          Code                        c1,
          Code                        c3
where (ia.Task_Status = c1.ID and c1.Code_Type_ID = 6)   -- to get the status description
    and (ia.Task_Type = c3.ID and c3.Code_Type_ID = 5)     -- to get the type description

The results should be:
DOCUMENT_ID   SCAN_DATE   INVOICE_NUMBER   INVOICE_DATE   INV_STATUS   INV_TYPE
11967804             8/4/2014       003060547                 7/30/2014          Ready               Not approved
11967527             8/4/2014       003060548                 7/30/2014          Ready               Approved
11967503             8/4/2014       003060549                 7/30/2014          Ready               Approved
...
11967526             8/4/2014       003060550                 7/30/2014          Ready               Approved


Thank you
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of edoc_admin
edoc_admin

ASKER

Thanks sdstuber.