SELECT
* FROM OPENQUERY (YLHT,'
SELECT
(SELECT DECODE(sd.sigininstate,1,''待签收'',''已签收'') from YLHT.tb_ope_sigininorder sd
where sd.taskorderno =R.taskorderno
and sd.state = 1
and rownum=1) as 签收状态
FROM YLHT.TB_SALE_TASKORDER R
')
go
这样单独测试可以运行出结果
签收状态
已签收
下面的语句加上这一句就不可以正确运行了
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 MAX(updatetime) FROM YLHT.tb_buy_assemrecord ar
where ar.type = 1
and ar.state = 2
and ar.taskorderno =R.TASKORDERNO) 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 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 业务员,
decode(c.changetype,1,''基本信息变更'',''退返转变更'')
as 变更类型,
(select sod.siginindate from YLHT.tb_ope_sigininorder sod
where sod.taskorderno=R.taskorderno
and sod.state = 1
and rownum=1) as 实际签收时间,
(select sod.sigininenteringdate from YLHT.tb_ope_sigininorder sod
where sod.taskorderno =R.taskorderno
and sod.state = 1
and rownum=1)as 签收录入时间,
R.createtime as 任务单制单时间,
(SELECT DECODE(sod.sigininstate,1,''待签收'',''已签收'') from YLHT.tb_ope_sigininorder sod
where sod.taskorderno =R.taskorderno
and sod.state = 1
and rownum=1)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
