有富基融通的大佬不，小弟有一事不明，求大佬幫忙解答下。
在報表中選擇查詢條件后，點擊查詢執行陳述句如下所示
-- 資料準備
begin
insert into tt_saledeptitem
select
shopid,shopname,managedeptid,deptid,groupname,
tradeprice,tradenumber,costvalue,discvalue,SaleValue,resalevalue,maoli,maolilv,
hbtradeprice,hbtradenumber,hbcostvalue,hbdiscvalue,hbsalevalue,hbresalevalue,hbmaoli,hbmaolilv
from
(select
a.shopid,a.shopname,a.managedeptid,a.deptid,a.groupname,
a.tradeprice,a.tradenumber,a.costvalue,a.discvalue,a.salevalue,a.resaleValue,a.maoli,a.maolilv,
c.tradeprice hbtradeprice,
a.tradenumber hbtradenumber,
c.costvalue hbcostvalue,
c.discvalue hbdiscvalue,
c.salevalue hbsalevalue,
c.resaleValue hbresalevalue,
c.maoli hbmaoli,
c.maolilv hbmaolilv
from
(select
a.ShopID,b.Name ShopName,
a.deptid managedeptid,
a.DeptID,c.Name GroupName,
a.tradeprice,a.tradenumber,
a.SaleValue,a.DiscValue,a.costvalue,
a.SaleValue-a.DiscValue resaleValue,
a.SaleValue-a.DiscValue-a.CostValue maoli,
case (a.SaleValue-a.DiscValue) when 0 then 0 else
100.00*(a.SaleValue-a.DiscValue-a.CostValue)/(a.SaleValue-a.DiscValue) end maolilv
from
(select
shopid,deptid,
sum(salevalue) salevalue,
sum(discvalue) discvalue,
sum(costvalue) costvalue,
sum(tradeprice) tradeprice,
sum(tradenumber) tradenumber
from
(select
a.shopid,
trunc(a.deptid/(select levelvalue from deptlevel where deptlevelid=1)) deptid, --‘1’為變數%deptlevelid%
sum(a.salevalue) salevalue,
sum(a.discvalue) discvalue,
sum(a.costvalue) costvalue,
sum(b.tradeprice) tradeprice,
sum(b.tradenumber) tradenumber
from
RPT_Saledept a,RPT_CashManageDept b
where
to_char(a.sdate,'yyyy-mm-dd') between '2020-05-01' and '2020-05-31' --查詢時間為變數%D1% %D2%
and to_char(a.sdate,'yyyymmdd')=b.sdate
and a.shopid=b.shopid
and trunc(a.deptid/(select levelvalue from deptlevel where deptlevelid=1))=b.SGroupID
and (a.salevalue-a.discvalue)<>0
group by a.shopid,deptid) j
group by
shopid,deptid) a,
Shop b,
Sgroup c
where
a.ShopID=b.ID
and a.DeptID=c.ID
and a.SaleValue-a.DiscValue<>0
order by
a.DeptID) a,
shop b,
(select
a.ShopID,
b.Name ShopName,
a.deptid managedeptid,
a.DeptID,c.Name GroupName,
a.tradeprice,a.tradenumber,
a.SaleValue,a.DiscValue,a.costvalue,
a.SaleValue-a.DiscValue resaleValue,
a.SaleValue-a.DiscValue-a.CostValue maoli,
case (a.SaleValue-a.DiscValue) when 0 then 0 else
100.00*(a.SaleValue-a.DiscValue-a.CostValue)/(a.SaleValue-a.DiscValue) end maolilv
from
(select
shopid,deptid,
sum(salevalue) salevalue,
sum(discvalue) discvalue,
sum(costvalue) costvalue,
sum(tradeprice) tradeprice,
sum(tradenumber) tradenumber
from
(select
a.shopid,
trunc(a.deptid/(select levelvalue from deptlevel where deptlevelid=1)) deptid,
sum(a.salevalue) salevalue,
sum(a.discvalue) discvalue,
sum(a.costvalue) costvalue,
sum(b.tradeprice) tradeprice,
sum(b.tradenumber) tradenumber
from
RPT_Saledept a,RPT_CashManageDept b
where
to_char(a.sdate,'yyyy-mm-dd') between to_char(add_months(to_date('2020-05-01','yyyy-mm-dd'),-1),'yyyy-mm-dd')
and to_char(add_months(to_date('2020-05-31','yyyy-mm-dd'),-1),'yyyy-mm-dd')
and to_char(a.sdate,'yyyymmdd')=b.sdate
and a.shopid=b.shopid
and trunc(a.deptid/(select levelvalue from deptlevel where deptlevelid=1))=b.SGroupID
and (a.salevalue-a.discvalue)<>0
group by
a.shopid,deptid) j
group by shopid,deptid) a,
Shop b,
Sgroup c
where
a.ShopID=b.ID
and a.DeptID=c.ID
and a.SaleValue-a.DiscValue<>0
order by
a.DeptID)c
where
a.shopid=b.id
and a.shopid=c.shopid
and a.deptid=c.deptid);
end;
-- 主查詢1 SQL
select
shopid,shopname,managedeptid,deptid,groupname,
tradeprice,tradenumber,costvalue,discvalue,SaleValue,resalevalue,maoli,maolilv,
hbtradeprice,hbtradenumber,hbcostvalue,hbdiscvalue,hbsalevalue,hbresalevalue,hbmaoli,hbmaolilv,
tradenumber-hbtradenumber tradenumberdiff,
case when (tradenumber-hbtradenumber)=0 then 1 else
(tradenumber-hbtradenumber)/hbtradenumber*100 end tradenumberdifflv,
costvalue-hbcostvalue costvaluediff,
case when (costvalue-hbcostvalue)=0 then 1 else
(costvalue-hbcostvalue)/hbcostvalue*100 end costvaluedifflv,
resalevalue-hbresalevalue salevaluediff,
case when (resalevalue-hbresalevalue)=0 then 1 else
(resalevalue-hbresalevalue)/hbresalevalue*100 end salevaluedifflv
from
tt_saledeptitem
where ( ( between '2020-05-01' and '2020-05-31' and SHOPID = 'A001' and = '1') ) --紅色這段是在報表設計器主查詢中不存在的。
order by
shopid,
deptid
--Error: General SQL error.
ORA-00936: 缺失運算式
在PL/SQL中臨時表執行是沒有問題的，主查詢執行報錯，去掉紅字條件后，可查詢出資料，百思不得其解，請大佬們幫我看看是什么原因
uj5u.com熱心網友回復：
between '2020-05-01' and '2020-05-31' 這句不全，要有個欄位名稱，來確定這個條件uj5u.com熱心網友回復：
這是主查詢陳述句，因為我之前寫的報表，在主查詢中也是沒有欄位名稱的，查詢條件也沒有欄位，只是做了變數替換而已，這就是我弄不懂的地方select
shopid,shopname,managedeptid,deptid,groupname,
tradeprice,tradenumber,costvalue,discvalue,SaleValue,resalevalue,maoli,maolilv,
hbtradeprice,hbtradenumber,hbcostvalue,hbdiscvalue,hbsalevalue,hbresalevalue,hbmaoli,hbmaolilv,
costvalue-hbcostvalue costvaluediff,
case when (costvalue-hbcostvalue)=0 then 1 else
(costvalue-hbcostvalue)/hbcostvalue*100 end costvaluedifflv,
resalevalue-hbresalevalue salevaluediff,
case when (resalevalue-hbresalevalue)=0 then 1 else
(resalevalue-hbresalevalue)/hbresalevalue*100 end salevaluedifflv
from
tt_saledeptitem
order by
shopid,
deptid
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/9986.html
標籤：非技術區