error ora-32031
emptyOct 18 2012 — edited Oct 19 2012Dear All
when running following query, system display an error ora-32031illegal reference of a query name in with clause . at line number 6(i have bold that line)
WITH Oin as (
select ITEM, SDATE, TXNTYPE, QTY, PRICE,ROW_NUMBER() OVER (PARTITION BY Item ORDER BY sDATE ) as rn
from Stock
where TxnType = 'IN'
), RunningTotals as (
select Item,Qty,Price,Qty as Total,0 as PrevTotal,rn from OIn where rn = 1
union all
select rt1.Item,oi.Qty,oi.Price,rt1.Total + oi.Qty,rt1.Total,oi.rn
from
RunningTotals rt1
inner join
OIn oi
on
rt1.Item = oi.Item and
rt1.rn = oi.rn - 1
), TotalOut as (
select Item,SUM(Qty) as Qty from Stock where TxnType='OUT' group by Item
)
select
rt.Item,SUM(CASE WHEN PrevTotal > out.Qty THEN rt.Qty ELSE rt.Total - out.Qty END * Price)
from
Rs rt
inner join
TotalOut out
on
rt.Item = out.Item
where
rt.Total > out.Qty
group by rt.Item
here the table structure.
CREATE TABLE STORE.STOCK
(
ITEM CHAR(3 BYTE) NOT NULL,
SDATE DATE NOT NULL,
TXNTYPE VARCHAR2(3 BYTE) NOT NULL,
QTY NUMBER NOT NULL,
PRICE NUMBER(10,2)
)
and sample data
insert into Stock values('ABC','1-apr-2012','IN', 200, 750.00)
insert into Stock values('ABC','5-apr-2012','OUT', 100 ,null )
insert into Stock values('ABC','10-apr-2012','IN', 50, 700.00)
insert into Stock values('ABC','16-apr-2012','IN', 75, 800.00)
insert into Stock values('ABC','25-apr-2012','OUT', 175, null )
insert into Stock values('XYZ','02-apr-2012','IN', 150, 350.00)
insert into Stock values('XYZ','08-apr-2012','OUT', 120 ,null )
insert into Stock values('XYZ','12-apr-2012','OUT', 10 ,null )
insert into Stock values('XYZ','24-apr-2012','IN', 90, 340.00)
please help me.
Thanks