L_BeginTime in Date,
L_EndTime in Date,
L_InOutType In varchar2,
L_ShipFlag In varchar2,
L_LoadFlag in varchar2,
L_Query in varchar2,
P_a number;select COUNT(Berth) into P_a from forecast Where to_char(Berth) in
(select distinct Forecast.Berth
from ShipInfo,Forecast,Application,CraneWorkInfo_3,GShipKHF,WorkDayInfo_3
where ShipInfo.ShipNameChn=Application.ShipNameChn
and ShipInfo.ShipNameEn=Application.ShipNameEn
and ShipInfo.ShipNameChn=Application.ShipNameChn
and Application.ApplyID=Forecast.ApplyID
and Forecast.ApplyID=CraneWorkInfo_3.ApplyID
and Forecast.BerthShiftTimes=CraneWorkInfo_3.BerthShiftTimes
and WorkDayInfo_3.ApplyID=Forecast.ApplyID
and WorkDayInfo_3.BerthShiftTimes=Forecast.BerthShiftTimes
and WorkDayInfo_3.WorkDate=CraneWorkInfo_3.WorkDate
and GShipKHF.ApplyID=CraneWorkInfo_3.ApplyID
and WorkDayInfo_3.DayLoad is not null
and GShipKHF.BerthShiftTimes=CraneWorkInfo_3.BerthShiftTimes
And CraneWorkInfo_3.WorkDate between ''||L_BeginTime||'' and ''||L_EndTime||'' ||
case when L_InOutType= '请选择' then Null else 'and ShipInfo.InOutType= '||L_InOutType||'' End ||
Case when L_ShipFlag Is Null then null else 'and Forecast.ShipFlag= '||L_ShipFlag||'' End ||
case when L_LoadFlag='请选择' then null else 'and Forecast.LoadFlag='||L_LoadFlag||'' end ||
case when L_Query= 'and (' then null else ''||L_Query||'' End );
就是报错ORACLE-01830:日期格式图片在转换整个输入字符之前结束,我把And CraneWorkInfo_3.WorkDate between ''||L_BeginTime||'' and ''||L_EndTime||''隐藏起来后,就报“无效数字”这个错误。请问大家这是为什么啊,我才接触Oracle没多久,折腾了半天也没搞定,拜托大家了!
L_EndTime in Date,
L_InOutType In varchar2,
L_ShipFlag In varchar2,
L_LoadFlag in varchar2,
L_Query in varchar2,
P_a number;select COUNT(Berth) into P_a from forecast Where to_char(Berth) in
(select distinct Forecast.Berth
from ShipInfo,Forecast,Application,CraneWorkInfo_3,GShipKHF,WorkDayInfo_3
where ShipInfo.ShipNameChn=Application.ShipNameChn
and ShipInfo.ShipNameEn=Application.ShipNameEn
and ShipInfo.ShipNameChn=Application.ShipNameChn
and Application.ApplyID=Forecast.ApplyID
and Forecast.ApplyID=CraneWorkInfo_3.ApplyID
and Forecast.BerthShiftTimes=CraneWorkInfo_3.BerthShiftTimes
and WorkDayInfo_3.ApplyID=Forecast.ApplyID
and WorkDayInfo_3.BerthShiftTimes=Forecast.BerthShiftTimes
and WorkDayInfo_3.WorkDate=CraneWorkInfo_3.WorkDate
and GShipKHF.ApplyID=CraneWorkInfo_3.ApplyID
and WorkDayInfo_3.DayLoad is not null
and GShipKHF.BerthShiftTimes=CraneWorkInfo_3.BerthShiftTimes
And CraneWorkInfo_3.WorkDate between ''||L_BeginTime||'' and ''||L_EndTime||'' ||
case when L_InOutType= '请选择' then Null else 'and ShipInfo.InOutType= '||L_InOutType||'' End ||
Case when L_ShipFlag Is Null then null else 'and Forecast.ShipFlag= '||L_ShipFlag||'' End ||
case when L_LoadFlag='请选择' then null else 'and Forecast.LoadFlag='||L_LoadFlag||'' end ||
case when L_Query= 'and (' then null else ''||L_Query||'' End );
就是报错ORACLE-01830:日期格式图片在转换整个输入字符之前结束,我把And CraneWorkInfo_3.WorkDate between ''||L_BeginTime||'' and ''||L_EndTime||''隐藏起来后,就报“无效数字”这个错误。请问大家这是为什么啊,我才接触Oracle没多久,折腾了半天也没搞定,拜托大家了!
解决方案 »
- 紧急求助,有没有将SQL Server的SQL翻译为Oracle的第三方组件?
- 小问题 ???
- 求助ORACLE 字符串判断是否以数字开头的
- 现有一个字段数据为1到N不等,是主键数据,还有个空的字符字段 我想修改空字符字段里的数据要求格式如下V0000001后面的那个1就是主键修改数据必须保持在8位
- 我有一个30W条的SQL脚本,怎么执行。
- 一个查询语句!偶搞不明白!
- 各位高手,请进吧!
- 客户端如何连接防火墙后的oracle实例?
- 小弟请教各位大侠:关于Unix下Oracle调用外部函数的问题
- sqlldr导入txt数据,在数据库成生一个log_record表(最好是存储过程) 急急急..!
- 清表产生错误
- 一个排序的问题
如果是varchar2,根据格式用to_char先转换L_BeginTime 和L_EndTime为相应的格式.
最好详细的贴下你的需求,你的sql看着很怪异.
请问下where后面跟case when是这样写的吗?
你的这个sql 到底是用动态的还是 直接像在plsql中的一般sql CraneWorkInfo_3.WorkDate between ''||L_BeginTime||'' and ''||L_EndTime||'' || WorkDate 的类型L_EndTime, L_BeginTime传进来的是什么类型的
And CraneWorkInfo_3.WorkDate between L_BeginTime and L_EndTime
and (L_InOutType= '请选择'or ShipInfo.InOutType=L_InOutType)
and (L_ShipFlag Is Null or Forecast.ShipFlag= L_ShipFlag)
and (L_LoadFlag='请选择' or Forecast.LoadFlag=L_LoadFlag)
(select distinct Forecast.Berth
from ShipInfo,Forecast,Application,CraneWorkInfo_3,GShipKHF,WorkDayInfo_3
where ShipInfo.ShipNameChn=Application.ShipNameChn
and ShipInfo.ShipNameEn=Application.ShipNameEn
and ShipInfo.ShipNameChn=Application.ShipNameChn
and Application.ApplyID=Forecast.ApplyID
and Forecast.ApplyID=CraneWorkInfo_3.ApplyID
and Forecast.BerthShiftTimes=CraneWorkInfo_3.BerthShiftTimes
and WorkDayInfo_3.ApplyID=Forecast.ApplyID
and WorkDayInfo_3.BerthShiftTimes=Forecast.BerthShiftTimes
and WorkDayInfo_3.WorkDate=CraneWorkInfo_3.WorkDate
and GShipKHF.ApplyID=CraneWorkInfo_3.ApplyID
and WorkDayInfo_3.DayLoad is not null
and GShipKHF.BerthShiftTimes=CraneWorkInfo_3.BerthShiftTimes
And CraneWorkInfo_3.WorkDate between L_BeginTime and L_EndTime || case when L_InOutType= '请选择' then Null else 'and ShipInfo.InOutType= '||L_InOutType||'' End ||
Case when L_ShipFlag Is Null then null else 'and Forecast.ShipFlag= '||L_ShipFlag||'' End ||
case when L_LoadFlag='请选择' then null else 'and Forecast.LoadFlag='||L_LoadFlag||'' end ||
case when L_Query= 'and (' then null else ''||L_Query||'' End );
And CraneWorkInfo_3.WorkDate between ''||L_BeginTime||'' and ''||L_EndTime||'' ||
case when L_InOutType= '请选择' then Null else 'and ShipInfo.InOutType= '||L_InOutType||'' End ||
Case when L_ShipFlag Is Null then null else 'and Forecast.ShipFlag= '||L_ShipFlag||'' End ||
case when L_LoadFlag='请选择' then null else 'and Forecast.LoadFlag='||L_LoadFlag||'' end ||
case when L_Query= 'and (' then null else ''||L_Query||'' End );
case when L_InOutType= '请选择' then Null else 'and ShipInfo.InOutType= '||L_InOutType||'' End ||是指如果
L_InOutType输入是‘请选择’,那么跟在where后面的这句就不存在,为Null,如果不是‘请选择’,就把
'and ShipInfo.InOutType= '||L_InOutType||''加在where后面。谢谢您了
And CraneWorkInfo_3.WorkDate between L_BeginTime and L_EndTime--时间介于输入时间之间
and (L_InOutType= '请选择'or ShipInfo.InOutType=L_InOutType) --L_InOutType='请选择'时这行相当于不存在,当不是'请选择'时,就限定了ShipInfo.InOutType=L_InOutType
ShipInfo.InOutType= (case L_InOutType when '请选择' then InOutType ShipInfo. else L_InOutType)
或者decode
ShipInfo.InOutType= decode(L_InOutType, '请选择' ,ShipInfo.L_InOutType,L_InOutType)
这个L_Query里面已经包含and,这个该怎么写呢?
ShipInfo.InOutType= (case L_InOutType when '请选择' then InOutType ShipInfo. else L_InOutType end)
你没明白我的意思
要是动态的拼接
你的许多地方就有误 单独这样的类型
create or replace procedure p1(v_id number,v_id2 number)
as
v_name varchar2(10);
begin
select name into v_name from tb where id between v_id and v_id2;
....
end;
CraneWorkInfo_3.WorkDate between L_BeginTime and L_EndTime
and (case
when L_InOutType= '请选择' then Null else ShipInfo.InOutType= L_InOutType End)
and (Case when L_ShipFlag Is Null then null else Forecast.ShipFlag= L_ShipFlag End )
and (case when L_LoadFlag='请选择' then null else Forecast.LoadFlag=L_LoadFlag end )
and (case when L_Query='' then null else L_Query End );
procedure AdvancedShipQuery
(
L_BeginTime in CraneWorkInfo_3.WorkDate%Type,
L_EndTime in CraneWorkInfo_3.WorkDate%Type,
L_InOutType In varchar2,
L_ShipFlag In varchar2,
L_LoadFlag in varchar2,
--L_TimeSection in nvarchar2,
L_Query in varchar2,
C_AdvancedInfo out C_AdvancedQuery
)
is
P_sql1 varchar2(4000);
P_sq2 varchar2(4000);
P_sq4 varchar2(4000);P_a number; --总共的泊位个数
is
begin
select COUNT(Berth)into P_a from forecast Where to_char(Berth) in
(select distinct Forecast.Berth
from ShipInfo,Forecast,Application,CraneWorkInfo_3,GShipKHF,WorkDayInfo_3
where ShipInfo.ShipNameChn=Application.ShipNameChn
and ShipInfo.ShipNameEn=Application.ShipNameEn
and ShipInfo.ShipNameChn=Application.ShipNameChn
and Application.ApplyID=Forecast.ApplyID
and Forecast.ApplyID=CraneWorkInfo_3.ApplyID
and Forecast.BerthShiftTimes=CraneWorkInfo_3.BerthShiftTimes
and WorkDayInfo_3.ApplyID=Forecast.ApplyID
and WorkDayInfo_3.BerthShiftTimes=Forecast.BerthShiftTimes
and WorkDayInfo_3.WorkDate=CraneWorkInfo_3.WorkDate
and GShipKHF.ApplyID=CraneWorkInfo_3.ApplyID
and WorkDayInfo_3.DayLoad is not null
and GShipKHF.BerthShiftTimes=CraneWorkInfo_3.BerthShiftTimes
And CraneWorkInfo_3.WorkDate between '''||L_BeginTime||''' and '''||L_EndTime||'''' ||
case when L_InOutType= '请选择' then Null else 'and ShipInfo.InOutType= '''||L_InOutType||'''' End ||
Case when L_ShipFlag Is Null then null else 'and Forecast.ShipFlag= '''||L_ShipFlag||'''' End ||
case when L_LoadFlag='请选择' then null else 'and Forecast.LoadFlag='''||L_LoadFlag||'''' end ||
case when L_Query= 'and (' then null else ''||L_Query||'' End ;
CraneWorkInfo_3.WorkDate between L_BeginTime and L_EndTime
and
(case when L_InOutType= '请选择' then Null else ShipInfo.InOutType= L_InOutType End)
and (Case when L_ShipFlag Is Null then null else Forecast.ShipFlag= L_ShipFlag End )
and (case when L_LoadFlag='请选择' then null else Forecast.LoadFlag=L_LoadFlag end )
and (case when L_Query='' then null else L_Query End );