SELECT cso.F_Key, cso.F_ServiceType,cso.F_ASPNode,cso.F_Productline,cso.F_InternalCloseTime,case WHEN F_InternalCloseTime>isnull(F_ComplaintTime,'1900-1-1') THEN F_InternalCloseTime
ELSE isnull(F_ComplaintTime,'1900-1-1') end MAXTime
FROM cr_cs_cso cso
left join CR_C_RewardPunishRecord rpd on rpd.f_cso = cso.f_key
left join CR_C_RewardPunishCriteria rpc on rpc.f_key = rpd.f_RewardPunishCriteria
WHERE 1=1 and rpd.f_status =50 and rpc.f_code in ('HTF01','HTF02','HTF03','HTF05','HTF06','HTF08','HTF09','HTF10','HTF13')
AND F_Priority IS NOT NULL
AND F_ComplaintType IS NOT NULL
AND F_ComplaintTime IS NOT NULL
AND (F_ComplaintType IN (4003,4006,4007,4008,4005,4009,4019,4034,4030,4029,4018) or F_Priority IN (70,60))
AND cso.F_Status in (160,170,180)
AND case WHEN F_InternalCloseTime>isnull(F_ComplaintTime,'1900-1-1') THEN F_InternalCloseTime
ELSE isnull(F_ComplaintTime,'1900-1-1') end BETWEEN '2012-06-27 00:00:00' AND '2012-07-26 23:59:59' and rpd.f_yearmonth='2012-07'
ORACLE怎么写?
ELSE isnull(F_ComplaintTime,'1900-1-1') end MAXTime
FROM cr_cs_cso cso
left join CR_C_RewardPunishRecord rpd on rpd.f_cso = cso.f_key
left join CR_C_RewardPunishCriteria rpc on rpc.f_key = rpd.f_RewardPunishCriteria
WHERE 1=1 and rpd.f_status =50 and rpc.f_code in ('HTF01','HTF02','HTF03','HTF05','HTF06','HTF08','HTF09','HTF10','HTF13')
AND F_Priority IS NOT NULL
AND F_ComplaintType IS NOT NULL
AND F_ComplaintTime IS NOT NULL
AND (F_ComplaintType IN (4003,4006,4007,4008,4005,4009,4019,4034,4030,4029,4018) or F_Priority IN (70,60))
AND cso.F_Status in (160,170,180)
AND case WHEN F_InternalCloseTime>isnull(F_ComplaintTime,'1900-1-1') THEN F_InternalCloseTime
ELSE isnull(F_ComplaintTime,'1900-1-1') end BETWEEN '2012-06-27 00:00:00' AND '2012-07-26 23:59:59' and rpd.f_yearmonth='2012-07'
ORACLE怎么写?
解决方案 »
- 程序中怎么取得"describe test"的结果集
- 一道数据库的笔试题,感觉有点难~~
- 怎么能查到一段时间内对某一个表都做了哪些操作?在线等
- 关于PLSQL的问题,请高手帮帮忙?
- 在线等待,这样的数据存储过程怎么写?分不够再加!急!!!!
- 关于时间的计算,如何计算时间1与时间2的差,并转换成分钟?
- [200分]在该存储过程传递的字符参数如何处理,我的处理老是出错!(急急急急急急急急急急急急急)
- 如何使用户在pl/sql中用户新建一个过程或表等时,弹出一输入框输入一些信息?
- 初学者的问题
- 如何查数据库中的一个过程的原来的定义??
- oracle存储过程中如何输出变量的值?
- 一个很简单的问题(关于oracle的用户管理)
SELECT cso.F_Key, cso.F_ServiceType,cso.F_ASPNode,cso.F_Productline,cso.F_InternalCloseTime,
case WHEN F_InternalCloseTime>nvl(F_ComplaintTime,to_date('1900-01-01','yyyy-mm-dd')) THEN F_InternalCloseTime
ELSE nvl(F_ComplaintTime,to_date('1900-01-01','yyyy-mm-dd')) end MAXTime
FROM cr_cs_cso cso
left join CR_C_RewardPunishRecord rpd on rpd.f_cso = cso.f_key
left join CR_C_RewardPunishCriteria rpc on rpc.f_key = rpd.f_RewardPunishCriteria
WHERE 1=1 and rpd.f_status =50 and rpc.f_code in ('HTF01','HTF02','HTF03','HTF05','HTF06','HTF08','HTF09','HTF10','HTF13')
AND F_Priority IS NOT NULL
AND F_ComplaintType IS NOT NULL
AND F_ComplaintTime IS NOT NULL
AND (F_ComplaintType IN (4003,4006,4007,4008,4005,4009,4019,4034,4030,4029,4018) or F_Priority IN (70,60))
AND cso.F_Status in (160,170,180)
and ((F_InternalCloseTime>isnull(F_ComplaintTime,to_date('1900-01-01','yyyy-mm-dd'))
and F_InternalCloseTime BETWEEN to_date('2012-06-27','yyyy-mm-dd') AND to_date('2012-07-26 23:59:59','yyyy-mm-dd hh24:mi:ss'))
or (
isnull(F_ComplaintTime,'1900-1-1') BETWEEN to_date('2012-06-27','yyyy-mm-dd') AND to_date('2012-07-26 23:59:59','yyyy-mm-dd hh24:mi:ss')
))
and rpd.f_yearmonth='2012-07' --你的这个语句是错的,不管在mssql还是在oracle,都不能这么用的
AND case WHEN F_InternalCloseTime>isnull(F_ComplaintTime,'1900-1-1') THEN F_InternalCloseTime
ELSE isnull(F_ComplaintTime,'1900-1-1') end BETWEEN '2012-06-27 00:00:00' AND '2012-07-26 23:59:59'
错误就在大于号那
SELECT Cso.f_Key,
Cso.f_Servicetype,
Cso.f_Aspnode,
Cso.f_Productline,
Cso.f_Internalclosetime,
CASE
WHEN f_Internalclosetime > Nvl(f_Complainttime, '1900-1-1') THEN
f_Internalclosetime
ELSE
Nvl(f_Complainttime, '1900-1-1')
END Maxtime
FROM Cr_Cs_Cso Cso
LEFT JOIN Cr_c_Rewardpunishrecord Rpd ON Rpd.f_Cso = Cso.f_Key
LEFT JOIN Cr_c_Rewardpunishcriteria Rpc ON Rpc.f_Key =
Rpd.f_Rewardpunishcriteria
WHERE 1 = 1
AND Rpd.f_Status = 50
AND Rpc.f_Code IN ('HTF01', 'HTF02', 'HTF03', 'HTF05', 'HTF06',
'HTF08', 'HTF09', 'HTF10', 'HTF13')
AND f_Priority IS NOT NULL
AND f_Complainttype IS NOT NULL
AND f_Complainttime IS NOT NULL
AND (f_Complainttype IN
(4003, 4006, 4007, 4008, 4005, 4009, 4019, 4034, 4030, 4029, 4018) OR
f_Priority IN (70, 60))
AND Cso.f_Status IN (160, 170, 180)
AND CASE WHEN
f_Internalclosetime > Nvl(f_Complainttime, '1900-1-1') THEN f_Internalclosetime ELSE Nvl(f_Complainttime, '1900-1-1') END AND f_Internalclosetime BETWEEN '2012-06-27 00:00:00' AND '2012-07-26 23:59:59' AND Rpd.f_Yearmonth = '2012-07'
SELECT Cso.f_Key,
Cso.f_Servicetype,
Cso.f_Aspnode,
Cso.f_Productline,
Cso.f_Internalclosetime,
CASE
WHEN f_Internalclosetime > Nvl(f_Complainttime, '1900-1-1') THEN
f_Internalclosetime
ELSE
Nvl(f_Complainttime, '1900-1-1')
END Maxtime
FROM Cr_Cs_Cso Cso
LEFT JOIN Cr_c_Rewardpunishrecord Rpd ON Rpd.f_Cso = Cso.f_Key
LEFT JOIN Cr_c_Rewardpunishcriteria Rpc ON Rpc.f_Key =
Rpd.f_Rewardpunishcriteria
WHERE 1 = 1
AND Rpd.f_Status = 50
AND Rpc.f_Code IN ('HTF01', 'HTF02', 'HTF03', 'HTF05', 'HTF06',
'HTF08', 'HTF09', 'HTF10', 'HTF13')
AND f_Priority IS NOT NULL
AND f_Complainttype IS NOT NULL
AND f_Complainttime IS NOT NULL
AND (f_Complainttype IN
(4003, 4006, 4007, 4008, 4005, 4009, 4019, 4034, 4030, 4029, 4018) OR
f_Priority IN (70, 60))
AND Cso.f_Status IN (160, 170, 180)
AND CASE WHEN
f_Internalclosetime > Nvl(f_Complainttime, '1900-1-1') THEN f_Internalclosetime ELSE Nvl(f_Complainttime, '1900-1-1') END AND f_Internalclosetime BETWEEN '2012-06-27 00:00:00' AND '2012-07-26 23:59:59' AND Rpd.f_Yearmonth = '2012-07'