| help with outer join [message #159190] |
Thu, 16 February 2006 11:12  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have an old view which ws using Max(ID)to determine the most recent record. But the app stopped using sequenses, replacing them with custom solution. So, for determing the most recent value I need to use the create date. That's OK, but I have multiple records with the same time stamp, so I still need to go to the max(ID) for these date stamp.
I have something like the query but it doesn't bring me back the records from the 2 tables if there's a no match in the third. When I put the pluses as show bellow, then I get Oracle error for outer join in subquery. I tried a sample like :
select a as name , (select addr1 from address where...) as addr1 , (select addr2 from addr where )addr2, etc.. but is this the best way?
Please, help me to fix that.
Thanks a lot, mj
SELECT e.ent_id,n.first_name,n.last_name,
a.addr1,a.addr2, a.addr3, a.city, a.state as state_code,
a.postal_code, a.country as country_code
from ent e, name n, address a
where e.ent_id (+)= n.ent_id
--and e.ent_id = 3
and e.ent_id (+)= a.ent_id
and a.addr_id =
(select max(addr_id) from address a2
where a2.ent_id = e.ent_id AND a2.sys_create_dt = (select max(a3.sys_create_dt) from address a3
where a3.ent_id = e.ent_id))
and n.name_id = (select max(name_id) from name n2
where n2.ent_id = e.ent_id AND n2.sys_create_dt = (select max(n3.sys_create_dt) from name n3
where n3.ent_id = e.ent_id))
|
|
|
|
| Re: help with outer join [message #159212 is a reply to message #159190] |
Thu, 16 February 2006 15:29  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm running out the door, and don't have time to answer this the right way, but most likely from skimming your question you need an analytic query where you can do a rank() over (order by create date desc, sequence desc) and do it all in an inline view of some kind, with the outer condition asking for a rank of 1. Relatively straightforard use of rank(), check the advanced sql forum for some examples if needed.
|
|
|
|
| Re: help with outer join [message #159266 is a reply to message #159212] |
Fri, 17 February 2006 01:38 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Look here
You can do something like:
SELECT MAX(id) KEEP (DENSE_RANK FIRST ORDER BY txn_dt DESC)
Note that this is an Aggregate function, not an Analytic function (as it has no OVER clause).
It does an aggregate MAX using a restricted set of rows that have the highest txn_dt.
_____________
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
