is
procedure Report_MaTolRunEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2)
is
MaTol nvarchar2(100);
vGroup nvarchar2(200);
begin
---vGroup:='GetTimeGroup(StartTime,'||'||vTime||'||')';
vGroup:='GetTimeGroup(StartTime,'||QUOTENAME(vTime,'''')||')';
if(Name='All') then
MaTol:='';
elsif(Name='Dept') then
MaTol:='DeptID='||QUOTENAME(vID,'''')||' AND';
elsif(Name='MaTolGrp') then
MaTol:='MaTolGrpID='||QUOTENAME(vID,'''')||' AND';
elsif(Name='MaTol') then
MaTol:='MaTolName='||QUOTENAME(vID,'''')||' AND';
end if;
if(vTime='') then
--vGroup:='DeptName||'||QUOTENAME('.','''')||'||MaTolGrpName||'||QUOTENAME('.','''')||'||MaTolName';
--vGroup:='DeptName|| QUOTENAME('.', '')||MaTolGrpName||QUOTENAME('.', '')||MaTolName';
vGroup:='DeptName'||QUOTENAME('.','')||'MaTolGrpName'||QUOTENAME('.','')||'MaTolName';
end if;
open rst for SELECT MaTolName,
sum(case status when QUOTENAME('运行','''') then vValue else 0 end) as RUNNING,
GetTimeStr(sum(case status when QUOTENAME('运行','''') then vValue else 0 end)) AS STRRUNNING,
sum(vValue) as sONLINE,GetTimeStr(sum(vValue)) AS STRONLINE,
cast(sum(case status when QUOTENAME('运行','''') then vValue else 0 end)*10000/sum(vValue) as decimal(10,2))/100 as RUNEFF,
sGroup From
(Select status,sum(statusTimeValue) AS vValue,
(DeptName|| QUOTENAME('.', '')||MaTolGrpName||QUOTENAME('.', '')||MaTolName) as MaTolName,
'||vGroup||' as sGroup
From StatusTable WHERE MaTol|| StartTime BETWEEN dbegin AND dend
Group by status,MaTolName,DeptName,MaTolGrpName,'||vGroup||') a group by MaTolName,sGroup;
end;
end;
各位高手,上面的存储过程错哪了,怎么老提示ORA-01858: 在要求输入数字处找到非数字字符,我把WHERE MaTol|| StartTime BETWEEN dbegin AND dend 改成WHERE StartTime BETWEEN dbegin AND dend 就没错误,什么原因啊?
procedure Report_MaTolRunEff
( rst out mytype ,
vID in nvarchar2,
Name in nvarchar2,
dbegin Date,
dend Date,
vTime nvarchar2)
is
MaTol nvarchar2(100);
vGroup nvarchar2(200);
begin
---vGroup:='GetTimeGroup(StartTime,'||'||vTime||'||')';
vGroup:='GetTimeGroup(StartTime,'||QUOTENAME(vTime,'''')||')';
if(Name='All') then
MaTol:='';
elsif(Name='Dept') then
MaTol:='DeptID='||QUOTENAME(vID,'''')||' AND';
elsif(Name='MaTolGrp') then
MaTol:='MaTolGrpID='||QUOTENAME(vID,'''')||' AND';
elsif(Name='MaTol') then
MaTol:='MaTolName='||QUOTENAME(vID,'''')||' AND';
end if;
if(vTime='') then
--vGroup:='DeptName||'||QUOTENAME('.','''')||'||MaTolGrpName||'||QUOTENAME('.','''')||'||MaTolName';
--vGroup:='DeptName|| QUOTENAME('.', '')||MaTolGrpName||QUOTENAME('.', '')||MaTolName';
vGroup:='DeptName'||QUOTENAME('.','')||'MaTolGrpName'||QUOTENAME('.','')||'MaTolName';
end if;
open rst for SELECT MaTolName,
sum(case status when QUOTENAME('运行','''') then vValue else 0 end) as RUNNING,
GetTimeStr(sum(case status when QUOTENAME('运行','''') then vValue else 0 end)) AS STRRUNNING,
sum(vValue) as sONLINE,GetTimeStr(sum(vValue)) AS STRONLINE,
cast(sum(case status when QUOTENAME('运行','''') then vValue else 0 end)*10000/sum(vValue) as decimal(10,2))/100 as RUNEFF,
sGroup From
(Select status,sum(statusTimeValue) AS vValue,
(DeptName|| QUOTENAME('.', '')||MaTolGrpName||QUOTENAME('.', '')||MaTolName) as MaTolName,
'||vGroup||' as sGroup
From StatusTable WHERE MaTol|| StartTime BETWEEN dbegin AND dend
Group by status,MaTolName,DeptName,MaTolGrpName,'||vGroup||') a group by MaTolName,sGroup;
end;
end;
各位高手,上面的存储过程错哪了,怎么老提示ORA-01858: 在要求输入数字处找到非数字字符,我把WHERE MaTol|| StartTime BETWEEN dbegin AND dend 改成WHERE StartTime BETWEEN dbegin AND dend 就没错误,什么原因啊?
=============================================
这样就不是布尔表达式了StartTime BETWEEN dbegin AND dend
==================================
这是布尔表达式。where 子句要求布尔表达式
更改
(MaTol ¦¦ StartTime) BETWEEN dbegin AND dend