把MS-SQL Server 一条语句翻译成oracle的送100分.

Question

ALTER  VIEW dbo.vw_itemsNode
AS
SELECT dbo.itemsNode.nodeID, dbo.itemsNode.itemsNo, dbo.itemsNode.dealAction, 
      dbo.itemsNode.dealDescription, dbo.itemsNode.status, dbo.itemsNode.disTime, 
      dbo.itemsNode.acceptTime, dbo.itemsNode.resolveTime, dbo.itemsNode.desUser, 
      departments_1.deptName, dbo.itemsNode.parentID, 
      departments_2.deptName AS sourceDeptName,
      to_date(to_char(dbo.itemsNode.resolveTime,'yyyymmddhh24mi'),'yyyymmddhh24mi')-
      to_date(to_char(dbo.itemsNode.disTime,'yyyymmddhh24mi'),'yyyymmddhh24mi') 
      as  currDate, 
      to_date(to_char(sysdate,'yyyymmddhh24mi'),'yyyymmddhh24mi')- 
      to_date(to_char(dbo.itemsNode.disTime,'yyyymmddhh24mi'),'yyyymmddhh24mi') AS 
      unCurrDate, departments_1.deptNo
FROM dbo.departments departments_1 INNER JOIN
      dbo.itemsNode ON 
      departments_1.deptID = dbo.itemsNode.desDeparts LEFT OUTER JOIN
      dbo.departments departments_2 INNER JOIN
      dbo.itemsNode itemsNode_1 ON 
      departments_2.deptID = itemsNode_1.desDeparts ON 
      dbo.itemsNode.parentID = itemsNode_1.nodeID

Answer 1

ALTER  VIEW dbo.vw_itemsNode
AS
SELECT itemsNode.nodeID, itemsNode.itemsNo, itemsNode.dealAction, 
       itemsNode.dealDescription, itemsNode.status, itemsNode.disTime, 
       itemsNode.acceptTime, itemsNode.resolveTime, itemsNode.desUser, 
      departments_1.deptName, itemsNode.parentID, 
      departments_2.deptName AS sourceDeptName,
      to_date(to_char(dbo.itemsNode.resolveTime,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')-
      to_date(to_char(dbo.itemsNode.disTime,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') 
      as  currDate, 
      to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')- 
      to_date(to_char(dbo.itemsNode.disTime,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') AS 
      unCurrDate, departments_1.deptNo
FROM departments ,departments_1 ,itemsNode 
Where 
      departments_1.deptID = itemsNode.desDeparts And departments. departments_2 
      =itemsNode. itemsNode_1 And  
      departments_2.deptID = itemsNode_1.desDeparts And 
      dbo.itemsNode.parentID = itemsNode_1.nodeID

Answer 2

ALTER VIEW vw_itemsNode
AS
SELECT 
    itemsNode.nodeID, 
    itemsNode.itemsNo, 
    itemsNode.dealAction, 
    itemsNode.dealDescription, 
    itemsNode.status, i
    temsNode.disTime, 
    itemsNode.acceptTime, 
    itemsNode.resolveTime, 
    itemsNode.desUser, 
    departments_1.deptName, 
    itemsNode.parentID, 
    departments_2.deptName AS sourceDeptName, 
    3600*(itemsNode.disTime-itemsNode.resolveTime) AS currDate, 
    3600*(itemsNode.disTime-sysdate) AS unCurrDate, 
    departments_1.deptNo
FROM 
    departments departments_1,
    itemsNode ,
    departments departments_2,
    itemsNode itemsNode_1
where
    departments_1.deptID = itemsNode.desDeparts 
    and
    departments_2.deptID = itemsNode_1.desDeparts(+)
    and
    itemsNode.parentID   = itemsNode_1.nodeID

Answer 3

DATEDIFF(mi,a,b)
------
trunc((b-a)*24*60)

Answer 4

LEFT OUTER JOIN=======>(+)

Answer 5

我按 libin_ftsafe(子陌红尘) 的语句创建的视图字段的个数要比sql server中的少.看来还是不对.本人很菜请大家指点.

Answer 6

一开始是用他作的,但在把oracle模块导入oracle数据库中有错误,就是提示上面的创建视图语句没有正常结束.还有对象名称已被占用等的错误.还有我的oracle是8i不支持outer join 等语句.

Answer 7

不知道 libin_ftsafe(子陌红尘) ( ) 给的对不对,有人能确认一下么?

Answer 8

ALTER  VIEW dbo.vw_itemsNode
AS
SELECT itemsNode.nodeID, 
       itemsNode.itemsNo,
       itemsNode.dealAction, 
       itemsNode.dealDescription，
       itemsNode.status,
       itemsNode.disTime,
       itemsNode.acceptTime,
       itemsNode.resolveTime,
       itemsNode.desUser, 
       departments_1.deptName,
       itemsNode.parentID, 
       departments_2.deptName AS sourceDeptName,
       to_char(itemsNode.resolveTime,'yyyymmddhh24mi') currDate,
       to_char(itemsNode.disTime,'yyyymmddhh24mi') currDate, 
       to_char(sysdate,'yyyymmddhh24mi') unCurrDate,
       to_char(itemsNode.disTime,'yyyymmddhh24mi') unCurrDate,
       departments_1.deptNo
FROM departments departments_1,
     itemsNode ,
     departments departments_2,
     itemsNode itemsNode_1
where departments_1.deptID = itemsNode.desDeparts  and
    departments_2.deptID = itemsNode_1.desDeparts(+) and
    itemsNode.parentID = itemsNode_1.nodeID

Answer 9

'yyyymmddhh24mi'改成'yyyy-mm-dd hh24:mi:ss'