请各位大佬有写报表经验的帮我看看，这是什么问题？[有富基的大佬更好]

[yoco zhang]

有富基融通的大佬不，小弟有一事不明，求大佬帮忙解答下。
在报表中选择查询条件后，点击查询执行语句如下所示
-- 数据准备
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中临时表执行是没有问题的，主查询执行报错，去掉红字条件后，可查询出数据，百思不得其解，请大佬们帮我看看是什么原因

[js14982]

between '2020-05-01' and '2020-05-31' 这句不全，要有个字段名称，来确定这个条件

[yoco zhang]

这是主查询语句，因为我之前写的报表，在主查询中也是没有字段名称的，查询条件也没有字段，只是做了变量替换而已，这就是我弄不懂的地方 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