SELECT a.Id, ISNULL(b.Name, '/') AS name, b.Sex, b.Organ, a.JoinDate, a.CancleDate,
CASE b.sex WHEN '1' THEN '男' WHEN '2' THEN '女' WHEN '3' THEN '不详' WHEN '0'
THEN '不详' END AS sexs,
(
CASE
(SELECT top 1
(case CensorType
when 56 then
case CensorResult
when 0 then '0'
when 1 then '1'
else '0'
end
when 55 then
case CensorResult
when 0 then '2'
when 1 then '3'
else '2'
end
end) as result
FROM censor
WHERE (CensorType = 56 or CensorType = 55 ) AND IdentifyID = a.id order by CensorType desc
)
WHEN '2' THEN '建立审批中'
WHEN '3' THEN '已建立'
WHEN '0' THEN '撤销审批中'
WHEN '1' THEN '已撤销'
else '建立审批中'
END)
AS state
FROM CaseRelate a LEFT OUTER JOIN
CaseRelateMan b ON a.Id = b.Crid order by name我在Sql Server里是这样写,但现在要转成Oracle,我不会转中间的case 这段,有哪些高手来帮帮忙,先在此谢过
CASE b.sex WHEN '1' THEN '男' WHEN '2' THEN '女' WHEN '3' THEN '不详' WHEN '0'
THEN '不详' END AS sexs,
(
CASE
(SELECT top 1
(case CensorType
when 56 then
case CensorResult
when 0 then '0'
when 1 then '1'
else '0'
end
when 55 then
case CensorResult
when 0 then '2'
when 1 then '3'
else '2'
end
end) as result
FROM censor
WHERE (CensorType = 56 or CensorType = 55 ) AND IdentifyID = a.id order by CensorType desc
)
WHEN '2' THEN '建立审批中'
WHEN '3' THEN '已建立'
WHEN '0' THEN '撤销审批中'
WHEN '1' THEN '已撤销'
else '建立审批中'
END)
AS state
FROM CaseRelate a LEFT OUTER JOIN
CaseRelateMan b ON a.Id = b.Crid order by name我在Sql Server里是这样写,但现在要转成Oracle,我不会转中间的case 这段,有哪些高手来帮帮忙,先在此谢过
解决方案 »
- 报错ORA-01652:无法通过128(在表空间TEMP)扩展TEMP段
- 关于安装oracle 9i没反应问题
- 请教shell中获取文件
- 高分!!!!写一个统计查询语句
- 紧急求助,某些汉字插入到oracle表中出现乱码问题
- 请教在procedure中调用 execute immediate 的问题
- 新人急问:可以在同一台机器上的一个系统下同时装oracle9i和sql server2000吗?谢谢!
- 到哪里去下载oracle?
- 大家好,帮我解决一下问题
- 在线急等,在oracle中怎么写类似sqlserver中这样的语句
- ora-12571错误怎么解决啊?
- Oracle创建临时表问题~~~~~~~~~~
是select top的不同
楼主去找一下oracle的select top写法就知道了
ISNULL(b.Name, '/') AS NAME,
b.Sex,
b.Organ,
a.JoinDate,
a.CancleDate,
CASE b.sex
WHEN '1' THEN
'男'
WHEN '2' THEN
'女'
WHEN '3' THEN
'不详'
WHEN '0' THEN
'不详'
END AS sexs,
(CASE (SELECT RESULT
FROM (SELECT (CASE CensorType
WHEN 56 THEN
CASE CensorResult
WHEN 0 THEN
'0'
WHEN 1 THEN
'1'
ELSE
'0'
END
WHEN 55 THEN
CASE CensorResult
WHEN 0 THEN
'2'
WHEN 1 THEN
'3'
ELSE
'2'
END
END) AS RESULT
FROM censor
WHERE (CensorType = 56 OR CensorType = 55) AND
IdentifyID = a.id
ORDER BY CensorType DESC)
WHERE rownum < 2)
WHEN '2' THEN
'建立审批中'
WHEN '3' THEN
'已建立'
WHEN '0' THEN
'撤销审批中'
WHEN '1' THEN
'已撤销'
ELSE
'建立审批中'
END) AS state
FROM CaseRelate a
LEFT OUTER JOIN CaseRelateMan b
ON a.Id = b.Crid
ORDER BY NAME
我现在用函数暂时实现
主要是Oracle里case后面的变量这样不行的