SELECT Depname,Employee,
(SELECT Codename FROM Employee WHERE CodeID = Employee) AS Operator,
"SYSDATE",SysTime,
case Doing WHEN N'0' THEN '登录'
WHEN N'1' THEN '进入'
WHEN N'2' THEN '读取'
WHEN N'3' THEN '新增'
WHEN N'4' THEN '生成'
WHEN N'5' THEN '修改'
WHEN N'6' THEN '删除'
WHEN N'7' THEN '保存'
WHEN N'8' THEN '打印'
WHEN N'9' THEN '审核'
WHEN N'10' THEN '家庭分离'
WHEN N'11' THEN '家庭合并'
WHEN N'12' THEN '复审'
WHEN N'13' THEN '红冲'
WHEN N'14' THEN '清空密码'
WHEN N'15' THEN '缴费'
WHEN N'16' THEN '撤消缴费'
END as OperType ,
case Succ WHEN 1 THEN '成功' WHEN 0 THEN '失败' END as good,
Module,ComputerName,DetailContent,ModuleName
FROM (SELECT Diary.*,(SELECT CodeID FROM Depart WHERE Codename=Depname) AS DepID,
(select ObjName from ObjID where objID=Module) as ModuleName FROM Diary) Diary
WHERE 1=1 AND substr(DepID,0,4)=:DepID
AND Diary.SysDate BETWEEN to_date(:starttime,'yyyy-mm-dd') AND to_date(:endtime,'yyyy-mm-dd')
order by Diary.SysDate desc,Diary.SysTime desc
(SELECT Codename FROM Employee WHERE CodeID = Employee) AS Operator,
"SYSDATE",SysTime,
case Doing WHEN N'0' THEN '登录'
WHEN N'1' THEN '进入'
WHEN N'2' THEN '读取'
WHEN N'3' THEN '新增'
WHEN N'4' THEN '生成'
WHEN N'5' THEN '修改'
WHEN N'6' THEN '删除'
WHEN N'7' THEN '保存'
WHEN N'8' THEN '打印'
WHEN N'9' THEN '审核'
WHEN N'10' THEN '家庭分离'
WHEN N'11' THEN '家庭合并'
WHEN N'12' THEN '复审'
WHEN N'13' THEN '红冲'
WHEN N'14' THEN '清空密码'
WHEN N'15' THEN '缴费'
WHEN N'16' THEN '撤消缴费'
END as OperType ,
case Succ WHEN 1 THEN '成功' WHEN 0 THEN '失败' END as good,
Module,ComputerName,DetailContent,ModuleName
FROM (SELECT Diary.*,(SELECT CodeID FROM Depart WHERE Codename=Depname) AS DepID,
(select ObjName from ObjID where objID=Module) as ModuleName FROM Diary) Diary
WHERE 1=1 AND substr(DepID,0,4)=:DepID
AND Diary.SysDate BETWEEN to_date(:starttime,'yyyy-mm-dd') AND to_date(:endtime,'yyyy-mm-dd')
order by Diary.SysDate desc,Diary.SysTime desc
sysdate "my_sysdate"
..
from ..
...
"sysdate" date default sysdate,
col1 number
);select * from t_tt;
做字段名没有什么问题呀?
TO_DATE(:ENDTIME, 'YYYY-MM-DD')
ORDER BY DIARY."SYSDATE" DESC, DIARY.SYSTIME DESC
修改如下:
SELECT DEPNAME,
EMPLOYEE,
(SELECT CODENAME FROM EMPLOYEE WHERE CODEID = EMPLOYEE) AS OPERATOR,
SYSDATE "SYSDATE",
SYSTIME,
case Doing WHEN N'0' THEN '登录'
WHEN N'1' THEN '进入'
WHEN N'2' THEN '读取'
WHEN N'3' THEN '新增'
WHEN N'4' THEN '生成'
WHEN N'5' THEN '修改'
WHEN N'6' THEN '删除'
WHEN N'7' THEN '保存'
WHEN N'8' THEN '打印'
WHEN N'9' THEN '审核'
WHEN N'10' THEN '家庭分离'
WHEN N'11' THEN '家庭合并'
WHEN N'12' THEN '复审'
WHEN N'13' THEN '红冲'
WHEN N'14' THEN '清空密码'
WHEN N'15' THEN '缴费'
WHEN N'16' THEN '撤消缴费'
END as OperType ,
case Succ WHEN 1 THEN '成功' WHEN 0 THEN '失败' END as good,
MODULE,
COMPUTERNAME,
DETAILCONTENT,
MODULENAME
FROM (SELECT DIARY.*,
(SELECT CODEID FROM DEPART WHERE CODENAME = DEPNAME) AS DEPID,
(SELECT OBJNAME FROM OBJID WHERE OBJID = MODULE) AS MODULENAME
FROM DIARY) DIARY
WHERE 1 = 1
AND SUBSTR(DEPID, 0, 4) = :DEPID
AND DIARY."SYSDATE" BETWEEN TO_DATE(:STARTTIME, 'yyyy-mm-dd') AND
TO_DATE(:ENDTIME, 'yyyy-mm-dd')
ORDER BY DIARY."SYSDATE" DESC, DIARY.SYSTIME DESC