我原先在SQL SERVER 里用datediff(d,date1,date2)来求两日期之间间隔的天数,但在ORACLE中用什么函数,我在网上找过了,没找到,请问有哪位大侠知道告诉我下?
解决方案 »
- 求高手解答(Oracle中两个查询结果的处理)
- 新加字段
- 怎样给xmltype列,指定xml schema?
- 单表多对多关联关系怎样查询某个节点下的所有子节点
- 问一个SQL问题
- toad 和 plsqldev 的比较
- 求助一个sql怎么写
- OO4O为何安装失败?环境WIN2003+ORACLE9i Release2 (急!!!!!!!)
- 字符集AMERICAN_AMERICA.US7ASCII是指UTF-7么
- flashback table时提示ora-00604错误
- oracle语句优化/*+ ALL_ROWS */
- oracle 能查sql server中的nchar类型的字段吗?ora-28500
CREATE VIEW dbo.V_PURCHASE
AS
SELECT dbo.TB_BUY_PLAN.vcPlanCode, dbo.TB_BUY_PLAN.vcGoodsCode,
dbo.TB_BUY_PLAN.vcGoodsName, dbo.TB_BUY_PLAN.vcStreamCode,
dbo.TB_BUY_PLAN_ITEM.vcGoodsName AS vcProductName,
dbo.TB_BUY_PLAN_ITEM.vcGoodsSize, dbo.TB_BUY_PLAN_ITEM.dPlanBuyDate,
dbo.TB_BUY_PLAN_ITEM.dPostDate,
dbo.TB_BUY_PLAN_ITEM.fCurentNumber AS fNumber,
dbo.TB_BUY_INCOMEBILL_ITEM.fInNumber,
dbo.TB_BUY_ORDER_ITEM.vcBuyOrderID,
dbo.TB_ORG_PERSON.vcPersonName AS vcPlaner,
TB_ORG_PERSON_1.vcPersonName, DATEDIFF(d,
dbo.TB_BUY_PLAN_ITEM.dPostDate, dbo.TB_BUY_INCOMEBILL.dAuditDate)
AS [date], dbo.TB_BUY_INCOMEBILL.vcStatus,
dbo.TB_BUY_INCOMEBILL.dPrePostDate, dbo.TB_BUY_INCOMEBILL.vcAspID,
dbo.TB_BUY_INCOMEBILL.vcBillType
FROM dbo.TB_BUY_INCOMEBILL_ITEM INNER JOIN
dbo.TB_BUY_ORDER_ITEM INNER JOIN
dbo.TB_ORG_PERSON INNER JOIN
dbo.TB_BUY_INCOMEBILL INNER JOIN
dbo.TB_ORG_PERSON TB_ORG_PERSON_1 ON
dbo.TB_BUY_INCOMEBILL.vcAspID = TB_ORG_PERSON_1.vcAspID ON
dbo.TB_ORG_PERSON.vcAspID = dbo.TB_BUY_INCOMEBILL.vcAspID INNER JOIN
dbo.TB_BUY_ORDER ON
TB_ORG_PERSON_1.vcPersonCode = dbo.TB_BUY_ORDER.vcBuyMaster AND
TB_ORG_PERSON_1.vcAspID = dbo.TB_BUY_ORDER.vcAspID AND
dbo.TB_BUY_INCOMEBILL.vcAspID = dbo.TB_BUY_ORDER.vcAspID AND
dbo.TB_BUY_INCOMEBILL.vcOrderID = dbo.TB_BUY_ORDER.vcBuyOrderID ON
dbo.TB_BUY_ORDER_ITEM.vcAspID = dbo.TB_BUY_INCOMEBILL.vcAspID AND
dbo.TB_BUY_ORDER_ITEM.vcBuyOrderID = dbo.TB_BUY_ORDER.vcBuyOrderID AND
dbo.TB_BUY_ORDER_ITEM.vcAspID = dbo.TB_BUY_ORDER.vcAspID INNER JOIN
dbo.TB_BUY_PLAN_ITEM INNER JOIN
dbo.TB_BUY_PLAN ON
dbo.TB_BUY_PLAN_ITEM.vcPlanID = dbo.TB_BUY_PLAN.vcPlanID AND
dbo.TB_BUY_PLAN_ITEM.vcAspID = dbo.TB_BUY_PLAN.vcAspID ON
dbo.TB_BUY_ORDER.vcComefrom = dbo.TB_BUY_PLAN.vcPlanID AND
dbo.TB_ORG_PERSON.vcAspID = dbo.TB_BUY_PLAN.vcAspID AND
dbo.TB_ORG_PERSON.vcPersonCode = dbo.TB_BUY_PLAN.vcPlaner AND
dbo.TB_BUY_INCOMEBILL.vcAspID = dbo.TB_BUY_PLAN.vcAspID AND
dbo.TB_BUY_ORDER_ITEM.vcGoodsCode = dbo.TB_BUY_PLAN_ITEM.vcGoodsCode AND
dbo.TB_BUY_INCOMEBILL.vcAspID = dbo.TB_BUY_PLAN_ITEM.vcAspID ON
dbo.TB_BUY_INCOMEBILL_ITEM.vcIncomeBillID = dbo.TB_BUY_INCOMEBILL.vcIncomeBillID
AND
dbo.TB_BUY_INCOMEBILL_ITEM.vcAspID = dbo.TB_BUY_INCOMEBILL.vcAspID AND
dbo.TB_BUY_INCOMEBILL_ITEM.vcGoodsCode = dbo.TB_BUY_PLAN_ITEM.vcGoodsCode
AND
dbo.TB_BUY_INCOMEBILL_ITEM.vcGoodsCode = dbo.TB_BUY_ORDER_ITEM.vcGoodsCode
我在ORACLE中这样改了:
SELECT TOYERP.TB_BUY_PLAN.vcPlanCode, TOYERP.TB_BUY_PLAN.vcGoodsCode,
TOYERP.TB_BUY_PLAN.vcGoodsName, TOYERP.TB_BUY_PLAN.vcStreamCode,
TOYERP.TB_BUY_PLAN_ITEM.vcGoodsName AS vcProductName,
TOYERP.TB_BUY_PLAN_ITEM.vcGoodsSize, TOYERP.TB_BUY_PLAN_ITEM.dPlanBuyDate,
TOYERP.TB_BUY_PLAN_ITEM.dPostDate,
TOYERP.TB_BUY_PLAN_ITEM.fCurentNumber AS fNumber,
TOYERP.TB_BUY_INCOMEBILL_ITEM.fInNumber,
TOYERP.TB_BUY_ORDER_ITEM.vcBuyOrderID,
TOYERP.TB_ORG_PERSON.vcPersonName AS vcPlaner,
TB_ORG_PERSON_1.vcPersonName,
DATE:=(TRUNC(TOYERP.TB_BUY_INCOMEBILL.dAuditDate,'DD')-TRUNC(TOYERP.TB_BUY_PLAN_ITEM.dPostDate,'DD'))
TOYERP.TB_BUY_INCOMEBILL.vcStatus,
TOYERP.TB_BUY_INCOMEBILL.dPrePostDate, TOYERP.TB_BUY_INCOMEBILL.vcAspID,
TOYERP.TB_BUY_INCOMEBILL.vcBillType
FROM TOYERP.TB_BUY_INCOMEBILL_ITEM INNER JOIN
TOYERP.TB_BUY_ORDER_ITEM INNER JOIN
TOYERP.TB_ORG_PERSON INNER JOIN
TOYERP.TB_BUY_INCOMEBILL INNER JOIN
TOYERP.TB_ORG_PERSON TB_ORG_PERSON_1 ON
TOYERP.TB_BUY_INCOMEBILL.vcAspID = TB_ORG_PERSON_1.vcAspID ON
TOYERP.TB_ORG_PERSON.vcAspID = TOYERP.TB_BUY_INCOMEBILL.vcAspID INNER JOIN
TOYERP.TB_BUY_ORDER ON
TB_ORG_PERSON_1.vcPersonCode = TOYERP.TB_BUY_ORDER.vcBuyMaster AND
TB_ORG_PERSON_1.vcAspID = TOYERP.TB_BUY_ORDER.vcAspID AND
TOYERP.TB_BUY_INCOMEBILL.vcAspID = TOYERP.TB_BUY_ORDER.vcAspID AND
TOYERP.TB_BUY_INCOMEBILL.vcOrderID = TOYERP.TB_BUY_ORDER.vcBuyOrderID ON
TOYERP.TB_BUY_ORDER_ITEM.vcAspID = TOYERP.TB_BUY_INCOMEBILL.vcAspID AND
TOYERP.TB_BUY_ORDER_ITEM.vcBuyOrderID = TOYERP.TB_BUY_ORDER.vcBuyOrderID AND
TOYERP.TB_BUY_ORDER_ITEM.vcAspID = TOYERP.TB_BUY_ORDER.vcAspID INNER JOIN
TOYERP.TB_BUY_PLAN_ITEM INNER JOIN
TOYERP.TB_BUY_PLAN ON
TOYERP.TB_BUY_PLAN_ITEM.vcPlanID = TOYERP.TB_BUY_PLAN.vcPlanID AND
TOYERP.TB_BUY_PLAN_ITEM.vcAspID = TOYERP.TB_BUY_PLAN.vcAspID ON
TOYERP.TB_BUY_ORDER.vcComefrom = TOYERP.TB_BUY_PLAN.vcPlanID AND
TOYERP.TB_ORG_PERSON.vcAspID = TOYERP.TB_BUY_PLAN.vcAspID AND
TOYERP.TB_ORG_PERSON.vcPersonCode = TOYERP.TB_BUY_PLAN.vcPlaner AND
TOYERP.TB_BUY_INCOMEBILL.vcAspID = TOYERP.TB_BUY_PLAN.vcAspID AND
TOYERP.TB_BUY_ORDER_ITEM.vcGoodsCode = TOYERP.TB_BUY_PLAN_ITEM.vcGoodsCode AND
TOYERP.TB_BUY_INCOMEBILL.vcAspID = TOYERP.TB_BUY_PLAN_ITEM.vcAspID ON
TOYERP.TB_BUY_INCOMEBILL_ITEM.vcIncomeBillID = TOYERP.TB_BUY_INCOMEBILL.vcIncomeBillID
AND
TOYERP.TB_BUY_INCOMEBILL_ITEM.vcAspID = TOYERP.TB_BUY_INCOMEBILL.vcAspID AND
TOYERP.TB_BUY_INCOMEBILL_ITEM.vcGoodsCode = TOYERP.TB_BUY_PLAN_ITEM.vcGoodsCode
AND
TOYERP.TB_BUY_INCOMEBILL_ITEM.vcGoodsCode = TOYERP.TB_BUY_ORDER_ITEM.vcGoodsCode
但也出现错误,请问怎么解决?
from dual;