Home » SQL & PL/SQL » SQL & PL/SQL » Having Problem in using rownum, please help, Thanks
Having Problem in using rownum, please help, Thanks [message #224821] Thu, 15 March 2007 10:49 Go to next message
rperananthan
Messages: 1
Registered: March 2007
Location: Redhill, Surrey
Junior Member
Hi,

select contract, part_no, wac, tot_wac, rownum,
(select round(max(rownum) * 0.05, 0)
from inventory_transaction_hist2
where contract = '2SIYM'
and transaction = 'WOISS'
and date_applied between add_months(sysdate, -12) and sysdate) as ABCC,
(select round(max(rownum) * 0.15, 0)
from inventory_transaction_hist2
where contract = '2SIYM'
and transaction = 'WOISS'
and date_applied between add_months(sysdate, -12) and sysdate) as ABCB,
(select round(max(rownum) * 0.80, 0)
from inventory_transaction_hist2
where contract = '2SIYM'
and transaction = 'WOISS'
and date_applied between add_months(sysdate, -12) and sysdate) as ABCA
from (select contract, part_no, round(IFSAPP.Inventory_Part_Config_API.Get_Inventory_Value_By_Method(contract, part_no, '*'), 2) wac, ifsapp.brs_function.GET_PART_TOTAL_WAC__(contract, part_no) tot_wac
from inventory_transaction_hist2
where contract = '2SIYM'
and transaction = 'WOISS'
and date_applied between add_months(sysdate, -12) and sysdate
group by contract, part_no
order by tot_wac)

To the above SQL, I want to add another column to display if rownum <= abcc then 'C', if rownum > abcc and <= abcb then 'B' and if rownum > abcb then 'A'

I have tried it with decode but getting error invalid column name.
Re: Having Problem in using rownum, please help, Thanks [message #224828 is a reply to message #224821] Thu, 15 March 2007 11:12 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What about ....
Select contract, part_no, wac, tot_wac, rn,
      case when rn<= abcc then  'C' 
           when rn >abcc and <= abcb Then  'B'
           when rn > abcb then 'C' End stats 
From (
select contract, part_no, wac, tot_wac, rownum rn,
(select round(max(rownum) * 0.05, 0)
from inventory_transaction_hist2
where contract = '2SIYM'
and transaction = 'WOISS'
and date_applied between add_months(sysdate, -12) and sysdate) as ABCC,
(select round(max(rownum) * 0.15, 0) 
from inventory_transaction_hist2
where contract = '2SIYM'
and transaction = 'WOISS'
and date_applied between add_months(sysdate, -12) and sysdate) as ABCB,
(select round(max(rownum) * 0.80, 0) 
from inventory_transaction_hist2
where contract = '2SIYM'
and transaction = 'WOISS'
and date_applied between add_months(sysdate, -12) and sysdate) as ABCA
from (select contract, part_no, round(IFSAPP.Inventory_Part_Config_API.Get_Inventory_Value_By_Method(contract, part_no, '*'), 2) wac, ifsapp.brs_function.GET_PART_TOTAL_WAC__(contract, part_no) tot_wac
from inventory_transaction_hist2
where contract = '2SIYM'
and transaction = 'WOISS'
and date_applied between add_months(sysdate, -12) and sysdate
group by contract, part_no
order by tot_wac)
) 



Thumbs Up
Rajuvan

[Updated on: Thu, 15 March 2007 11:13]

Report message to a moderator

Previous Topic: need how to make of exists operator
Next Topic: Query Failing with Invalid Number
Goto Forum:
  


Current Time: Sun Dec 04 10:49:31 CST 2016

Total time taken to generate the page: 0.13101 seconds