Problem solve Get help with specific problems with your technologies, process and projects.

Using inline view to better query joined tables

One reader asks how to best query data from two joined Oracle tables.

I have a table called ENTITY with columns srcID, entID, occurrence and histflag. I want to get the rows with the max value in column 'occurrence' for each entID.  I have accomplished this with the following:


select *
from ENTITY T1
where T1.srcID  = 2          
and   T1.histflag = 'N'          
and   T1.occurrence =
      (select max(T2.occurrence)
       from ENTITY T2
       where T2 .srcID =   T1.srcID
       and    T2 .entID =   T1.ent_id);

I am able to get the row for each entID that has the highest value in the column 'occurrence'. But once I get the max row for each entID, I want to join this entity row to the ACCOUNT table to grab some additional data.  This is my select statement:
 

select *
from ENTITY T1, ACCOUNT T3
where T1.srcID  = 2          
and   T1.histflag = 'N'          
and   T1.occurrence =
      (select max(T2.occurrence)
       from ENTITY T2
       where T2 .srcID =   T1.srcID
       and    T2 .entID =   T1.ent_id)
and T1.srcID =   T3.srcID
and T1.entID =   T3.ent_id);

I am not getting my expected results.  I will need to join other tables to this as well as I am pulling data from many source tables together. I need to get this first one working before I can proceed. Thanks so much.

What you can do is move the query to get the latest entity into an inline view. Then it should be much easier to add the other tables to this result set, so long as they have a valid relationship, of course.

select E.*, A.*
from (
select T1.*
from ENTITY T1
where T1.srcID  = 2          
and   T1.histflag = 'N'          
and   T1.occurrence =
      (select max(T2.occurrence)
       from ENTITY T2
       where T2 .srcID =   T1.srcID
       and    T2 .entID =   T1.entID)) E
INNER JOIN account A
ON A.entID = E.entID;

Dig Deeper on Oracle and SQL

SearchDataManagement
SearchBusinessAnalytics
SearchSAP
SearchSQLServer
TheServerSide.com
SearchDataCenter
SearchContentManagement
SearchHRSoftware
Close