程序代码：
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME ='V_TASK_REPORT_TIMELY')
DROP VIEW V_TASK_REPORT_TIMELY
GO
CREATE VIEW V_TASK_REPORT_TIMELY
AS
select * from openquery(YLHT,'
SELECT
R.consigndate as 托运日期,
R.TASKORDERNO 任务单号,
R.printorderno as 分单号,
R.waybill as 主单号,
(select sum(nvl(g.prepieceqty,0)) from YLHT.tb_sale_taskorder_goodsdetail g where g.taskorderno=R.taskorderno) as 总预计件数,
(select sum(nvl(tg.preweight, 0)) from YLHT.tb_sale_taskorder_goodsdetail tg
where tg.taskorderno = R.taskorderno) as 总预计重量,
(select sum(greatest (nvl(tg.convertplanweight, 0) ,nvl(tg.preweight, 0) ,nvl(tg.convertweight, 0) ))
from YLHT.tb_sale_taskorder_goodsdetail tg
where tg.taskorderno = R.taskorderno ) as 总计费重量,
to_number(NVL(R.REBATEAMOUNT,0)) AS 回扣金额,
to_number(R.TOTALAMOUNT) AS 总应收,
(select p.productname from YLHT.tb_sys_product p where p.productid = R.mainproduct) as 开单干线,
(SELECT PT.PRODUCTNAME
FROM YLHT.TB_SYS_PRODUCT PT
WHERE PT.PRODUCTID =(SELECT RDD.TASKPRODUCTID
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL RDD
WHERE RDD.ROUTEORDER =NVL((SELECT MIN(RD.ROUTEORDER)
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL RD
WHERE RD.TASKORDERNO = R.TASKORDERNO
AND RD.ROUTETYPE = 3
AND RD.ROWSTATE <> 0),
-1)
AND RDD.TASKORDERNO = R.TASKORDERNO
AND RDD.ROUTETYPE = 3
AND RDD.ROWSTATE <> 0
AND ROWNUM = 1)) AS 实际干线,
(SELECT DECODE(PT.TRANSPORTWAY,1,''空运'',2,''汽运'',3,''铁运'',''其他'') AS 运输方式
FROM YLHT.TB_SYS_PRODUCT PT
WHERE PT.PRODUCTID =(SELECT RDD.TASKPRODUCTID
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL RDD
WHERE RDD.ROUTEORDER =NVL((SELECT MIN(RD.ROUTEORDER)
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL RD
WHERE RD.TASKORDERNO = R.TASKORDERNO
AND RD.ROUTETYPE = 3
AND RD.ROWSTATE <> 0),
-1)
AND RDD.TASKORDERNO = R.TASKORDERNO
AND RDD.ROUTETYPE = 3
AND RDD.ROWSTATE <> 0
AND ROWNUM = 1)) AS 实际运输方式,
nvl((SELECT MIN(D.RESOURCEDATE) AS RESOURCEDATE
FROM YLHT.TB_BUY_ASSEMRECORD AR,
YLHT.TB_BUY_ASSEMPLAN AP,
YLHT.TB_BUY_BUYORDER B,
YLHT.TB_BUY_BUYORDER_DETAIL D
WHERE AR.STATE > 0
AND AP.STATE > 0
AND D.ROWSTATE > 0
AND AP.ASSEMPLANID = AR.ASSEMPLANID
AND B.BUYORDERNO = AR.BUYSCHEDULEORDER
AND B.BUYORDERNO = D.BUYORDERNO
AND AP.MAINPRODUCT = AR.PRODUCTID
AND AR.TASKORDERNO = R.TASKORDERNO) ,
(SELECT MIN(SD.VALIDDATE) AS RESOURCEDATE
FROM YLHT.TB_BUY_ASSEMRECORD AR,
YLHT.TB_BUY_ASSEMPLAN AP,
YLHT.TB_SALE_SCHEDULEORDER SD
WHERE AR.STATE > 0
AND AP.STATE > 0
AND AP.ASSEMPLANID = AR.ASSEMPLANID
AND AP.MAINPRODUCT = AR.PRODUCTID
AND AR.TASKORDERNO = R.TASKORDERNO
AND AR.BUYSCHEDULEORDER = SD.SCHEDULEORDERNO) ) as 出港日期,
DECODE(R.GOODSCONTROL,1,''物控'',0,''不物控'') AS 物控,
(SELECT WM_CONCAT(TBA.BUYSCHEDULEORDER)
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL TSTR
INNER JOIN YLHT.TB_BUY_ASSEMRECORD TBA
ON TBA.TASKORDERNO= TSTR.TASKORDERNO
AND TBA.PRODUCTID = TSTR.TASKPRODUCTID
WHERE TBA.TASKORDERNO =R.TASKORDERNO
AND TSTR.ROUTETYPE = 3
AND TSTR.ROWSTATE > 0
AND TBA.STATE > 0
GROUP BY TSTR.TASKORDERNO) AS 采购单号,
CASE WHEN R.TASKTYPE>=8 THEN
(SELECT NETPOINTNAME FROM YLHT.TB_BASE_NETPOINT NET WHERE )
ELSE (select c.customername from YLHT.tb_buy_assemrecord ar,
YLHT.tb_buy_assemplan ap,
YLHT.tb_sys_customer c
where ar.state > 0
and ap.state(+) > 0
and ap.assemplanid(+) = ar.assemplanid
and ar.taskorderno = R.TASKORDERNO
and ar.routeorder = (select max(rd.routeorder)
from YLHT.tb_sale_taskorder_routedetail rd
where rd.taskorderno = ar.taskorderno
and rd.rowstate > 0
and rd.routetype in (5,6))
and c.customerid = ar.customerid
and rownum = 1)
END AS 实际提派代理,
(select MAX(updatetime) FROM YLHT.tb_buy_assemrecord ar
where ar.type = 1
and ar.state = 2
and ar.taskorderno =R.TASKORDERNO) AS 配载生效时间,
(select from YLHT.tb_base_netpoint n where ) as 始发站,
(select from YLHT.tb_base_netpoint n where ) as 中转站,
(select from YLHT.tb_base_netpoint n where ) as 目的站,
(select from YLHT.tb_base_netpoint n where ) as 来源网点,
(SELECT NETPOINTNAME FROM YLHT.TB_BASE_NETPOINT NET WHERE ) AS 目的网点,
(SELECT C.CUSTOMERNAME FROM YLHT.TB_SYS_CUSTOMER C WHERE C.CUSTOMERID = R.SENDAGENCY) AS 开单提派代理,
(select c1.customername from YLHT.tb_sys_customer c1 where c1.customerid = R.customerid ) as 客商名称,
(select from YLHT.tb_sys_org s where R.deptid=) as 受理部门,
(select u1.username from YLHT.tb_sys_user u1 where R.salesman = u1.userid and u1.state>0 and rownum=1) as 业务员,
R.createtime as 任务单制单时间,
DECODE(R.AIRBILLCOLLECTION, 1, ''拼单'', 0, ''主单'') AS 是否拼票,
decode(R.issplitorder,0,''母单'',''子单'') as 母子单
FROM YLHT.TB_SALE_TASKORDER R
LEFT JOIN YLHT.tb_sys_customer CU ON CU.CUSTOMERID=R.CUSTOMERID
left join YLHT.tb_sale_taskorderchange c
on c.taskorderid=R.taskorderid
and c.goodscontrol2 is not null
and c.state=7
and c.createtime IN (select max(tc.createtime)
from YLHT.tb_sale_taskorderchange tc
where tc.taskorderid = R.taskorderid
group by tc.taskorderid)
WHERE R.STATE<>1
AND R.CONSIGNDATE>=to_date(''2015-10-06'',''yyyy-mm-dd'')
')
GO
SELECT * FROM V_TASK_REPORT_TIMELY
运行结果如下
托运日期 任务单号 分单号 主单号 总预计件数 总预计重量 总计费重量
00:00.0 RW2016080100866 6 DD2016080100184-01 63 320 320
00:00.0 RW2016080100870 9 DD2016080200201-01 10 188 188
00:00.0 RW2016080100875 14 DD2016080200200-01 4 50 50
00:00.0 RW2016080100876 15 DD2016080300230-01 11 105 248.04
00:00.0 RW2016080100880 2002 DD2016080100186-01 1 30000 30000
00:00.0 RW2016080100883 19 DD2016080200222-01 1 18 20
00:00.0 RW2016080100887 2007 DD2016080100191-01 1 30000 30000
00:00.0 RW2016080100889 1001 DD2016080200200-01 110 1153 1624.04
00:00.0 RW2016080100895 1006 DD2016080200201-01 2 71.5 71.5
00:00.0 RW2016080100901 1011 DD2016080200199-02 21 516 516
00:00.0 RW2016080100865 5 DD2016080100184-02 19 760 760
00:00.0 RW2016080100903 1013 NULL 1 12 12
00:00.0 RW2016080100905 1015 DD2016080200200-01 2 43.5 43.5
00:00.0 RW2016080100906 1016 DD2016080200200-01 11 158.5 158.5
00:00.0 RW2016080100909 1018 DD2016080200202-02 50 275 380
00:00.0 RW2016080100912 4002 NULL 6 130 166.67
00:00.0 RW2016080100921 3005 NULL 1 11.7 11.7
00:00.0 RW2016080100922 1026 DD2016080200196-01 16 128.5 137.56
00:00.0 RW2016080100926 3007 NULL IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME ='V_TASK_REPORT_TIMELY')
DROP VIEW V_TASK_REPORT_TIMELY
GO
CREATE VIEW V_TASK_REPORT_TIMELY
AS
select * from openquery(YLHT,'
SELECT
R.consigndate as 托运日期,
R.TASKORDERNO 任务单号,
R.printorderno as 分单号,
R.waybill as 主单号,
(select sum(nvl(g.prepieceqty,0)) from YLHT.tb_sale_taskorder_goodsdetail g where g.taskorderno=R.taskorderno) as 总预计件数,
(select sum(nvl(tg.preweight, 0)) from YLHT.tb_sale_taskorder_goodsdetail tg
where tg.taskorderno = R.taskorderno) as 总预计重量,
(select sum(greatest (nvl(tg.convertplanweight, 0) ,nvl(tg.preweight, 0) ,nvl(tg.convertweight, 0) ))
from YLHT.tb_sale_taskorder_goodsdetail tg
where tg.taskorderno = R.taskorderno ) as 总计费重量,
to_number(NVL(R.REBATEAMOUNT,0)) AS 回扣金额,
to_number(R.TOTALAMOUNT) AS 总应收,
(select p.productname from YLHT.tb_sys_product p where p.productid = R.mainproduct) as 开单干线,
(SELECT PT.PRODUCTNAME
FROM YLHT.TB_SYS_PRODUCT PT
WHERE PT.PRODUCTID =(SELECT RDD.TASKPRODUCTID
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL RDD
WHERE RDD.ROUTEORDER =NVL((SELECT MIN(RD.ROUTEORDER)
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL RD
WHERE RD.TASKORDERNO = R.TASKORDERNO
AND RD.ROUTETYPE = 3
AND RD.ROWSTATE <> 0),
-1)
AND RDD.TASKORDERNO = R.TASKORDERNO
AND RDD.ROUTETYPE = 3
AND RDD.ROWSTATE <> 0
AND ROWNUM = 1)) AS 实际干线,
(SELECT DECODE(PT.TRANSPORTWAY,1,''空运'',2,''汽运'',3,''铁运'',''其他'') AS 运输方式
FROM YLHT.TB_SYS_PRODUCT PT
WHERE PT.PRODUCTID =(SELECT RDD.TASKPRODUCTID
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL RDD
WHERE RDD.ROUTEORDER =NVL((SELECT MIN(RD.ROUTEORDER)
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL RD
WHERE RD.TASKORDERNO = R.TASKORDERNO
AND RD.ROUTETYPE = 3
AND RD.ROWSTATE <> 0),
-1)
AND RDD.TASKORDERNO = R.TASKORDERNO
AND RDD.ROUTETYPE = 3
AND RDD.ROWSTATE <> 0
AND ROWNUM = 1)) AS 实际运输方式,
nvl((SELECT MIN(D.RESOURCEDATE) AS RESOURCEDATE
FROM YLHT.TB_BUY_ASSEMRECORD AR,
YLHT.TB_BUY_ASSEMPLAN AP,
YLHT.TB_BUY_BUYORDER B,
YLHT.TB_BUY_BUYORDER_DETAIL D
WHERE AR.STATE > 0
AND AP.STATE > 0
AND D.ROWSTATE > 0
AND AP.ASSEMPLANID = AR.ASSEMPLANID
AND B.BUYORDERNO = AR.BUYSCHEDULEORDER
AND B.BUYORDERNO = D.BUYORDERNO
AND AP.MAINPRODUCT = AR.PRODUCTID
AND AR.TASKORDERNO = R.TASKORDERNO) ,
(SELECT MIN(SD.VALIDDATE) AS RESOURCEDATE
FROM YLHT.TB_BUY_ASSEMRECORD AR,
YLHT.TB_BUY_ASSEMPLAN AP,
YLHT.TB_SALE_SCHEDULEORDER SD
WHERE AR.STATE > 0
AND AP.STATE > 0
AND AP.ASSEMPLANID = AR.ASSEMPLANID
AND AP.MAINPRODUCT = AR.PRODUCTID
AND AR.TASKORDERNO = R.TASKORDERNO
AND AR.BUYSCHEDULEORDER = SD.SCHEDULEORDERNO) ) as 出港日期,
DECODE(R.GOODSCONTROL,1,''物控'',0,''不物控'') AS 物控,
(SELECT WM_CONCAT(TBA.BUYSCHEDULEORDER)
FROM YLHT.TB_SALE_TASKORDER_ROUTEDETAIL TSTR
INNER JOIN YLHT.TB_BUY_ASSEMRECORD TBA
ON TBA.TASKORDERNO= TSTR.TASKORDERNO
AND TBA.PRODUCTID = TSTR.TASKPRODUCTID
WHERE TBA.TASKORDERNO =R.TASKORDERNO
AND TSTR.ROUTETYPE = 3
AND TSTR.ROWSTATE > 0
AND TBA.STATE > 0
GROUP BY TSTR.TASKORDERNO) AS 采购单号,
CASE WHEN R.TASKTYPE>=8 THEN
(SELECT NETPOINTNAME FROM YLHT.TB_BASE_NETPOINT NET WHERE )
ELSE (select c.customername from YLHT.tb_buy_assemrecord ar,
YLHT.tb_buy_assemplan ap,
YLHT.tb_sys_customer c
where ar.state > 0
and ap.state(+) > 0
and ap.assemplanid(+) = ar.assemplanid
and ar.taskorderno = R.TASKORDERNO
and ar.routeorder = (select max(rd.routeorder)
from YLHT.tb_sale_taskorder_routedetail rd
where rd.taskorderno = ar.taskorderno
and rd.rowstate > 0
and rd.routetype in (5,6))
and c.customerid = ar.customerid
and rownum = 1)
END AS 实际提派代理,
(select siginindate from YLHT.tb_ope_sigininorder os
where
os.taskorderno= R.taskorderno
and os.state = 1
and rownum=1) as 实际签收时间,
(select MAX(updatetime) FROM YLHT.tb_buy_assemrecord ar
where ar.type = 1
and ar.state = 2
and ar.taskorderno =R.TASKORDERNO) AS 配载生效时间,
(select from YLHT.tb_base_netpoint n where ) as 始发站,
(select from YLHT.tb_base_netpoint n where ) as 中转站,
(select from YLHT.tb_base_netpoint n where ) as 目的站,
(select from YLHT.tb_base_netpoint n where ) as 来源网点,
(SELECT NETPOINTNAME FROM YLHT.TB_BASE_NETPOINT NET WHERE ) AS 目的网点,
(SELECT C.CUSTOMERNAME FROM YLHT.TB_SYS_CUSTOMER C WHERE C.CUSTOMERID = R.SENDAGENCY) AS 开单提派代理,
(select c1.customername from YLHT.tb_sys_customer c1 where c1.customerid = R.customerid ) as 客商名称,
(select from YLHT.tb_sys_org s where R.deptid=) as 受理部门,
(select u1.username from YLHT.tb_sys_user u1 where R.salesman = u1.userid and u1.state>0 and rownum=1) as 业务员,
R.createtime as 任务单制单时间,
DECODE(R.AIRBILLCOLLECTION, 1, ''拼单'', 0, ''主单'') AS 是否拼票,
decode(R.issplitorder,0,''母单'',''子单'') as 母子单
FROM YLHT.TB_SALE_TASKORDER R
LEFT JOIN YLHT.tb_sys_customer CU ON CU.CUSTOMERID=R.CUSTOMERID
left join YLHT.tb_sale_taskorderchange c
on c.taskorderid=R.taskorderid
and c.goodscontrol2 is not null
and c.state=7
and c.createtime IN (select max(tc.createtime)
from YLHT.tb_sale_taskorderchange tc
where tc.taskorderid = R.taskorderid
group by tc.taskorderid)
WHERE R.STATE<>1
AND R.CONSIGNDATE>=to_date(''2015-10-06'',''yyyy-mm-dd'')
')
GO
SELECT * FROM V_TASK_REPORT_TIMELY
消息 103，级别 15，状态 1，过程 V_TASK_REPORT_TIMELY，第 4 行
以 '
SELECT
R.consigndate as 托运日期,
R.TASKORDERNO
任务单号,
R.printorderno as 分单号,
R.waybill as 主单号,
' 开头的 字符串 太长。最大长度为 8000。
消息 208，级别 16，状态 1，第 1 行
对象名 'V_TASK_REPORT_TIMELY' 无效。
只是多了一个字段
(select siginindate from YLHT.tb_ope_sigininorder os
where
os.taskorderno= R.taskorderno
and os.state = 1
and rownum=1) as 实际签收时间,
