Oracle - Joined tables using Analytical Function

edoc_admin
edoc_admin used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
Since your status and type descriptions have repeat values, scalar subqueries should be efficient

They also have the advantage that they can be added to your existing query easily

SELECT document_id,
       scan_date,
       invoice_number,
       invoice_date,
       (SELECT description
          FROM code c
         WHERE c.id = inv_status AND c.code_type_id = 6)
           inv_status,
       (SELECT description
          FROM code c
         WHERE c.id = inv_type AND c.code_type_id = 5)
           inv_type
  FROM (  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);

Open in new window

Author

Commented:
Thanks sdstuber.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial