• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 50
  • Last Modified:

Oracle view to get a field value based on another row

I have an oracle table for Documents with fileds DocNo , Revision, State , Plant
  Only some revisions  for a given docno has the plant value  
   sample data
   DocNo  Revision  State Plant
    Doc1       1            1      
    Doc1       2            1         P1
     Doc1      3             2    
    Doc2       1            1        
     Doc2       2            3        P2

  I need a view with following
   For rows where Plant value present  return state as state * 10
   For rows with no plant value , look DocNo and return plant value   from the row  for same DocNo with plant value

  View should be like
DocNo  Revision  State Plant
    Doc1       1            1           P1
    Doc1       2            10         P1
     Doc1      3             2         P1
    Doc2       1            1          P2      
     Doc2       2            30        P2
Sam OZ
Sam OZ
1 Solution
slightwv (䄆 Netminder) Commented:
Try this:
select docno, revision, case when plant is not null then state*10 else state end,
	first_value(plant) over(partition by docno order by docno) plant
from tab1

Open in new window

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now