Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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
0
edoc_admin
Asked:
edoc_admin
1 Solution
 
sdstuberCommented:
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

0
 
edoc_adminAuthor Commented:
Thanks sdstuber.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now