Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

error ora-32031

emptyOct 18 2012 — edited Oct 19 2012
Dear 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 16 2012
Added on Oct 18 2012
11 comments
224 views