troubleshooting Question

Oracle - Joined tables using Analytical Function

Avatar of edoc_admin
edoc_admin asked on
Oracle Database
2 Comments1 Solution380 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros