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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER