Tuesday, February 7, 2017

oracle query: get rows number and create conditions

to create the conditions in which the same data does not need to be displayed but still display different data

example:
item code item name value reward total
001 pen 500 500 1000
001 pen 600 500 1100
001 pen 100 500 600

after query create:
select  Case When WO_ID > 1 Then '' Else item_code End As item_code,
  Case When WO_ID > 1 Then '' Else item_name End As item_name,
  Case When WO_ID > 1 Then 0 Else value End As value,
  Case When WO_ID > 1 Then 0 Else reward End As reward,
  Case When WO_ID > 1 Then value Else (value+reward) End As total
from (select
  item_code, item_name, value, reward,
  ROW_NUMBER() Over (Partition By item_code Order By item_code) As  WO_ID
from tabelx)

result:
item code item name value reward total
001 pen 500 500 1000


600
600


100
100

No comments:

Post a Comment