由于如下语句中对日期进行了判断，期望把日期做成变量然后在程序中引用。避免输入错误。
Select
to_date( TO_CHAR( SYSDATE-1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) AS 导入日期,
to_char(SYSDATE-1, 'yyyy' ) AS 年,
TblStock.FName As 商品名称,
TblStock.FShortCode as 商品简码,
Sum(Case
WHEN TSFH.FDate < to_date( TO_CHAR( trunc(sysdate-1, 'd') + 1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) THEN
coalesce(TSFH.FInQuanty, 0) - coalesce(TSFH.FOutQuanty, 0)
Else
0
END) As 期初数量,
Sum(Case
WHEN TSF.FFinishDate >= to_date( TO_CHAR( trunc(sysdate-1, 'd') + 7, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) and
TSFH.FDate <= to_date( TO_CHAR( trunc(sysdate-1, 'd') + 7, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) THEN
coalesce(TSFH.FInQuanty, 0) - coalesce(TSFH.FOutQuanty, 0)
Else
0
END) As 期末数量,
Sum(Case
WHEN TSFH.FDate >= to_date( TO_CHAR( trunc(sysdate-1, 'd') + 1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) And
TSFH.FDate <= to_date( TO_CHAR( trunc(sysdate-1, 'd') + 7, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) and
Coalesce(PM.FInternal, 0) = 0 THEN
coalesce(TSFH.FOutQuanty, 0)
Else
0
End) As 本期出库数量,
Round(Sum(Case
WHEN TSFH.FDate >= TO_DATE('2019-07-01', 'YYYY-MM-DD') And
TSFH.FDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD') and
Coalesce(PM.FInternal, 0) = 0 THEN
coalesce(TSFH.FOutQuanty, 0)
Else
0
End) /
(DateDiff(DD,
TO_DATE('2019-07-01', 'YYYY-MM-DD'),
TO_DATE('2019-07-11', 'YYYY-MM-DD')) + 1),
2) As 日平均数量,
case Sum(Case
WHEN TSFH.FDate >=to_date( TO_CHAR( trunc(sysdate-1, 'd') + 1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) And
TSFH.FDate <=to_date( TO_CHAR( trunc(sysdate-1, 'd') + 7, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) and
Coalesce(PM.FInternal, 0) = 0 THEN
coalesce(TSFH.FOutAmount, 0)
Else
0
End)
when 0 then
NULL
else
(((Sum(Case
WHEN TSFH.FDate <to_date( TO_CHAR( trunc(sysdate-1, 'd') + 1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) THEN
coalesce(TSFH.FInAmount, 0) - coalesce(TSFH.FOutAmount, 0)
Else
0
END) *
(DATEDIFF(dd,
TO_DATE('2019-07-01', 'YYYY-MM-DD'),
TO_DATE('2019-07-11', 'YYYY-MM-DD')) + 2) +
Sum((Case
When tsfh.FDate >= TO_DATE('2019-07-01', 'YYYY-MM-DD') and
tsfh.FDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD') then
TSFH.FInAmount - TSFH.FOutAmount
else
0
end) *
(DATEDIFF(dd,
tsfh.FDate,
TO_DATE('2019-07-11', 'YYYY-MM-DD')) + 1)))) /
(DATEDIFF(dd,
TO_DATE('2019-07-01', 'YYYY-MM-DD'),
TO_DATE('2019-07-11', 'YYYY-MM-DD')) + 2)) *
(DateDiff(DD,
TO_DATE('2019-07-01', 'YYYY-MM-DD'),
TO_DATE('2019-07-11', 'YYYY-MM-DD')) + 1) /
(Sum(Case
WHEN TSFH.FDate >= TO_DATE('2019-07-01', 'YYYY-MM-DD') And
TSFH.FDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD') and
Coalesce(PM.FInternal, 0) = 0 THEN
coalesce(TSFH.FOutAmount, 0)
Else
0
End))
End As 库存周转天数,
Case Sum(Case
WHEN TSF.FFinishDate >= TO_DATE('2019-07-11', 'YYYY-MM-DD') and
TSFH.FDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD') THEN
coalesce(TSFH.FInQuanty, 0) - coalesce(TSFH.FOutQuanty, 0)
Else
0
END)
When 0 Then
Null
Else
(Case
When (Round(Sum(Case
WHEN TSFH.FDate >= TO_DATE('2019-07-01', 'YYYY-MM-DD') And
TSFH.FDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD') and
Coalesce(PM.FInternal, 0) = 0 THEN
coalesce(TSFH.FOutQuanty, 0)
Else
0
End) /
(DateDiff(DD,
TO_DATE('2019-07-01', 'YYYY-MM-DD'),
TO_DATE('2019-07-11', 'YYYY-MM-DD')) + 1),
2)) <= 0 Then
-1
Else
(Sum(Case
WHEN TSF.FFinishDate >= TO_DATE('2019-07-11', 'YYYY-MM-DD') and
TSFH.FDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD') THEN
coalesce(TSFH.FInQuanty, 0) - coalesce(TSFH.FOutQuanty, 0)
Else
0
END)) / (Round(Sum(Case
WHEN TSFH.FDate >= TO_DATE('2019-07-01', 'YYYY-MM-DD') And
TSFH.FDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD') and
Coalesce(PM.FInternal, 0) = 0 THEN
coalesce(TSFH.FOutQuanty, 0)
Else
0
End) /
(DateDiff(DD,
TO_DATE('2019-07-01', 'YYYY-MM-DD'),
TO_DATE('2019-07-11', 'YYYY-MM-DD')) + 1),
2))
End)
End As 预期销售天数
FROM tblStockFlow TSF
Inner Join tblStockFlowHistory TSFH
On TSFH.FStockFlowID = TSF.FID
Inner Join tblStock
On TSF.FStockID = tblStock.FId
Inner Join tblStockClass
On tblStock.FStockClassID = tblStockClass.FID
Inner join TblMark
on TblStock.FMarkid = TblMark.Fid
Inner Join TblUnit
On TSF.FSupplierID = Tblunit.Fid
Inner Join TblPriceType
On TSF.FPriceTypeID = TblPricetype.Fid
Inner Join TblWareHouse
ON TSF.FWareHouseID = TblWareHouse.FID
Left join tblProductoutDetail Pd
on TSFH.Fbilltype = 7
and TSFH.FBillDetailID = PD.FID
Left Join tblProductOut PM
on PD.FHeaderID = PM.FID
Left join tblInArrangeDetail IAD
on TSFH.Fbilltype = 1
and TSFH.FBillDetailID = IAD.FID
Left Join tblInArrange IAM
on IAD.FHeaderID = IAM.FID
Left join tblInReturnDetail IRD
on TSFH.Fbilltype = 2
and TSFH.FBillDetailID = IRD.FID
Left Join tblInReturn IRM
on IRD.FHeaderID = IRM.FID
Left Join TblDepart
ON TblWareHouse.FDepartID = tblDepart.FID
Inner join TblCompany
ON TblDepart.FCompanyID = TblCompany.FID
Left Join tblFinancialClass
on tblStockClass.FFinancialClassID = tblFinancialClass.FID
Left Join tblUnit OriSupplier
On TSF.FOriSupplierID = OriSupplier.FID
WHERE TSFH.FDATE <= TO_DATE('2019-07-11', 'YYYY-MM-DD')
And TSF.FInDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD')
And (TSF.FFinishDate >= TO_DATE('2019-07-01', 'YYYY-MM-DD'))
GROUP BY TblStock.FName, TblStock.FShortCode, TblStock.FMeasurement
但是进行验证的时候写成如下形式，验证不通过
declare
v_ksrq date := to_date( TO_CHAR( trunc(sysdate-1, 'd') + 1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' );
-- v_ksrq date;
--:= round(sysdate,'dd');
begin
--select
select
to_date( TO_CHAR( SYSDATE-1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) AS 导入日期,
to_char(SYSDATE-1, 'yyyy' ) AS 年,
TblStock.FName As 商品名称,
TblStock.FShortCode as 商品简码,
Sum(Case
WHEN TSFH.FDate <v_ksrq THEN
coalesce(TSFH.FInQuanty, 0) - coalesce(TSFH.FOutQuanty, 0)
Else
0
END) As 期初数量
FROM tblStockFlow TSF inner join tblStockFlowHistory TSFH
On TSFH.FStockFlowID = TSF.FID
Inner Join tblStock
On TSF.FStockID = tblStock.FId;
end;
提示
[65000][6550] ORA-06550: 第 7 行, 第 7 列: PLS-00428: 在此 SELECT 语句中缺少 INTO 子句 java.lang.RuntimeException: Error : 6550, Position : 196, Sql = declare v_ksrq date := to_date( TO_CHAR( trunc(sysdate-1, 'd') + 1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ); -- v_ksrq dat ...
[65000][6550] ORA-06550: 第 7 行, 第 7 列: PLS-00428: 在此 SELECT 语句中缺少 INTO 子句
————————————————————————
pl/sql中不能执行单独的SQL语句，select的结果必须into到一个变量中
评论
有用 0在查询中需要使用很多日期作为条件，例如下面的这种，想把日期做成变量来提高通用性，提高代码输入正确率，减少错误？
TSF.FFinishDate >= to_date( TO_CHAR( trunc(sysdate-1, 'd') + 7, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) and
TSFH.FDate <= to_date( TO_CHAR( trunc(sysdate-1, 'd') + 7, 'yyyy-mm-dd' ), 'yyyy-mm-dd' )
tsfh.FDate >= TO_DATE('2019-07-01', 'YYYY-MM-DD') and
tsfh.FDate <= TO_DATE('2019-07-11', 'YYYY-MM-DD')
评论 有用 0老师 我知道问题在那里，也知道了通过这种方式进行赋值。但是我的问题是，如果进行这种改动，报错。
declare v_ksrq date ; --定义变量 begin select to_date( TO_CHAR( trunc(sysdate-1, 'd') + 1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' )ksrq into v_ksrq from dual; --变量赋值 select to_date( TO_CHAR( SYSDATE-1, 'yyyy-mm-dd' ), 'yyyy-mm-dd' ) AS 导入日期, to_char(SYSDATE-1, 'yyyy' ) AS 年, TblStock.FName As 商品名称, TblStock.FShortCode as 商品简码, Sum(Case WHEN TSFH.FDate < v_ksrq THEN coalesce(TSFH.FInQuanty, 0) - coalesce(TSFH.FOutQuanty, 0) Else 0 END) As 期初数量 FROM tblStockFlow TSF inner join tblStockFlowHistory TSFH On TSFH.FStockFlowID = TSF.FID Inner Join tblStock On TSF.FStockID = tblStock.FId; end; 系统提示： [65000][6550] ORA-06550: 第 7 行, 第 7 列: PLS-00428: 在此 SELECT 语句中缺少 INTO 子句 java.lang.RuntimeException: Error : 6550, Position : 171, Sql = declare v_ksrq date ; beg ...
评论 有用 0这个报错信息里面已经提示的很明确了，顺藤摸瓜即可，另外从贴出的信息来看，貌似日期函数并不熟悉，建议先熟悉下，匿名块的写法和日期函数，然后在试一次，更有收获，如果还有问题，咱们再看看
评论 有用 0
墨值悬赏