asked on

Oracle view to get same value for a field for a given value of another filed

I have an Oracle 11 table Docs with following fields ( and other fields also)

DocNo StateNo Location
Doc1 10 Loc1
Doc1 360 Loc2
Doc1 12 Loc1
Doc2 1000 Loc3
Doc2 5 Loc2
Doc3 360
Doc3 5 Loc1

I need a view with additional field LocationDerived . For a DocNo , look for the highest stateNo and keep that value of location as LocationDerived for all the rows of the same DocNo (Please note that null is the LocationDerived for Doc3)
The view should result like this

DocNo StateNo Location LocationDerived
Doc1 10 Loc1 Loc2
Doc1 360 Loc2 Loc2
Doc1 12 Loc1 Loc2
Doc2 1000 Loc3 Loc3
Doc2 5 Loc2 Loc3
Doc2 5 Loc2 Loc3
Doc3 360
Doc3 5 Loc1

OMC2000

select t1.DocNo, t1.StateNo, t1.Location, t3.Location as LocationDerived
from Docs t1 left outer join 
(select  DocNo, max(StateNo) StateNo from Docs  t0 group by  DocNo) t2 on t1.DocNo = t2.DocNo
left outer join Docs  t3 on  t3.DocNo = t2.DocNo and t3.StateNo = t2.StateNo

Open in new window

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial