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
edoc_adminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
edoc_adminAuthor Commented:
Thanks sdstuber.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.