SQL代码运行不了，求指教 - 编程论坛

#2

tlliqi2016-09-02 09:04

分段查一下

#3

awinna2016-09-10 11:05

程序代码：

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 实际签收时间,

#4

awinna2016-09-10 11:08

程序代码：

SELECT  * FROM OPENQUERY (YLHT,'
select
      R.taskorderno as 任务单,
       (select os.siginindate from YLHT.tb_ope_sigininorder os
                              where
                             os.taskorderno= R.taskorderno
                               and os.state = 1
                               and rownum=1) as 实际签收时间,
                                state
                                from YLHT.TB_SALE_TASKORDER R


')
任务单    实际签收时间    STATE
RW2016080201096    35:30.0    8
RW2016080201097    22:30.0    8
RW2016080201098    34:30.0    8
RW2016080201099    47:30.0    8
RW2016080201100    20:52.0    8
RW2016080201101    21:30.0    8
RW2016080201102    20:00.0    8
RW2016080201103    20:04.0    8
RW2016080201104    28:30.0    8
RW2016080201105    53:30.0    8
RW2016080201106    35:30.0    8
RW2016080201107    03:30.0    8
RW2016080201108    34:30.0    8
RW2016080201109    35:30.0    8
RW2016080201110    NULL    0
RW2016080201111    20:00.0    8

#5

awinna2016-09-10 11:10

是不是时间要其他函数作用一下才能出正确结果呢？求高手能有时间解答

#6

awinna2016-09-10 11:17

程序代码：

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 任务单制单时间,
       (SELECT MAX(J.REALFINISHTIME) FROM YLHT.TB_OPE_JOBORDER J
                               INNER JOIN YLHT.TB_OPE_JOBORDER_DETAIL JD
                                  ON JD.JOBORDERNO=J.JOBORDERNO
                                LEFT JOIN YLHT.tb_sys_customer C ON C.CUSTOMERID=J.JOBORG
                               WHERE J.STATE>0
                                 AND J.JOBORDERTYPE=11
                                 AND C.CUSTOMERTYPE IN (12,22)
                                 AND JD.TASKORDERNO=R.TASKORDERNO) 提货完成时间,
       CASE WHEN R.TASKTYPE IN (4,8,9) THEN
                 (SELECT MAX(J.REALFINISHTIME) FROM YLHT.TB_OPE_JOBORDER J
                                              INNER JOIN YLHT.TB_OPE_JOBORDER_DETAIL JD
                                                      ON JD.JOBORDERNO=J.JOBORDERNO
                                              WHERE J.STATE>0
                                                AND J.JOBORDERTYPE=2
                                                AND J.PRODUCTID=5
                                                AND JD.TASKORDERNO=R.TASKORDERNO)
            ELSE
                 (SELECT MAX(J.REALFINISHTIME) FROM YLHT.TB_OPE_JOBORDER J
                                                                INNER JOIN YLHT.TB_OPE_JOBORDER_DETAIL JD
                                                                        ON JD.JOBORDERNO=J.JOBORDERNO
                                                                LEFT JOIN YLHT.tb_sys_customer C ON C.CUSTOMERID=J.JOBORG
                                                                WHERE J.STATE>0
                                                                  AND J.JOBORDERTYPE=12
                                                                  AND C.CUSTOMERTYPE IN (12,22)
                                                                  AND JD.TASKORDERNO=R.TASKORDERNO)
        END AS          派送时间,
       (select sd.siginindate from YLHT.tb_ope_sigininorder sd
                             where sd.taskorderno=R.taskorderno
                               and sd.state = 1
                               and rownum=1) as 实际签收时间,

       (SELECT DECODE(sd.sigininstate,1,''待签收'',''已签收'') from YLHT.tb_ope_sigininorder sd
                                                            where sd.taskorderno =R.taskorderno
                                                              and sd.state = 1
                                                              and rownum=1)as 签收状态,
       (select sd.sigininname from YLHT.tb_ope_sigininorder sd
                         where sd.taskorderno =R.taskorderno
                           and sd.state = 1 and rownum=1) as 签收人,
       decode(c.changetype,1,''基本信息变更'',''退返转变更'')  as 变更类型,
       c.createtime as 申请时间,
       c.updatetime as 审核时间,
       c.confirmtime as 确认时间,
       (select u1.username from YLHT.tb_sys_user u1 where R.salesman = u1.userid and u1.state>0 and rownum=1) as 业务员,
       (select u2.username  from YLHT.tb_sys_customer_permission p1
                                 inner join YLHT.tb_sys_user u2
                                         on p1.userid = u2.userid
                                        and p1.permissiontype = 2
                                        and p1.state=1
                           where = p1.customerid = R.customerid
                             and p1.state>0
                             and rownum=1)as 跟单员,
       R.createtime as 任务单制单时间,
       DECODE(R.AIRBILLCOLLECTION, 1, ''拼单'', 0, ''主单'') AS 是否拼票,
       decode(R.issplitorder,0,''母单'',''子单'') as 母子单
       (select min(to_char(nvl(tf.realstarttime,(select tfr.realleavertime
                                                   from YLHT.tb_base_truckfollowrecord tfr
                                                  where tfr.state=1
                                                    and tfr.scheduleorderno = trd.buyscheduleorder
                                                    and tfr.routeorder =(select min(tfr2.routeorder)
                                                                           from YLHT.tb_base_truckfollowrecord tfr2
                                                                          where tfr2.state=1
                                                                            and tfr2.scheduleorderno =tfr.scheduleorderno)
                                                                            and rownum=1)),
                         ''yyyy-mm-dd hh24:mi:ss'')) as realstarttime
          from YLHT.tb_sale_taskorder_routedetail route
               left join YLHT.tb_buy_assemrecord trd
                      on route.taskorderno = trd.taskorderno
                     and route.taskproductid = trd.taskproductid
                     and trd.fromnetpointid = route.taskfromnetpoint
                     and trd.tonetpointid = route.tasktonetpoint
                     and (trd.routeorder = route.routeorder or trd.buyorderdetailid is not null)
                     and trd.state>0
               left join YLHT.tb_buy_buyorder_detail tdt
                      on tdt.buyorderno = trd.buyscheduleorder
                     and tdt.fromnetpointid = route.taskfromnetpoint
                     and tdt.tonetpointid = route.tasktonetpoint
                     and (tdt.taskorderno is null or  tdt.taskorderno=route.taskorderno)
                     and tdt.productid=trd.productid
               left join YLHT.tb_sys_product pt
                      on pt.productid=tdt.productid
                     and pt.transportproduct=1
               left join YLHT.tb_buy_flightfollowrecord tf
                          on tf.productid=tdt.productid
                         and tf.state = 1
                         and tf.followdate = tdt.resourcedate
         where route.rowstate <> 0
           and route.routetype=3
           and route.taskorderno = R.taskorderno) 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

这是全部的代码，只是运行不了，学渣无力

#7

awinna2016-09-10 17:48

程序代码：

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 总应收,
     FN_GET_DICTNAME(''TASK_TYPE'', R.TASKTYPE) 任务类型,
       fn_get_dictname(''ORDER_STATE'',R.state) as 单据状态,
          (select FN_GET_DICTNAME(''TRANSPORT_TYPE'', P.TRANSPORTWAY)
          from YLHT.tb_sys_product p where p.productid =R.mainproduct) 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 WM_CONCAT(FN_GET_DICTNAME(''CUSTOMER_TYPE'', T.COLUMN_VALUE))
          FROM TABLE(FN_SPLIT(CU.CUSTOMERTYPE, '','')) T) AS 客户类型,
       (select from YLHT.tb_sys_org s where R.deptid=) as 受理部门,


       (select min(to_char(nvl(tf.planstarttime,(select tfr.preleavertime
                                                    from YLHT.tb_base_truckfollowrecord tfr
                                                   where tfr.state=1
                                                     and  tfr.scheduleorderno = trd.buyscheduleorder
                                                     and tfr.routeorder =(select min(tfr2.routeorder)
                                                                            from YLHT.tb_base_truckfollowrecord tfr2
                                                                           where tfr2.state=1
                                                                             and tfr2.scheduleorderno =tfr.scheduleorderno)
                                                                             and rownum=1)),
                         ''yyyy-mm-dd hh24:mi:ss'') ) as planstarttime
          from YLHT.tb_sale_taskorder_routedetail route
               left join YLHT.tb_buy_assemrecord trd
                      on route.taskorderno = trd.taskorderno
                     and route.taskproductid = trd.taskproductid
                     and trd.fromnetpointid = route.taskfromnetpoint
                     and trd.tonetpointid = route.tasktonetpoint
                     and (trd.routeorder = route.routeorder or trd.buyorderdetailid is not null)
                     and trd.state>0
               left join YLHT.tb_buy_buyorder_detail tdt
                      on tdt.buyorderno = trd.buyscheduleorder
                     and tdt.fromnetpointid = route.taskfromnetpoint
                     and tdt.tonetpointid = route.tasktonetpoint
                     and (tdt.taskorderno is null or  tdt.taskorderno=route.taskorderno)
                     and tdt.productid=trd.productid
               left join YLHT.tb_sys_product pt
                      on pt.productid=tdt.productid
                     and pt.transportproduct=1
               left join YLHT.tb_buy_flightfollowrecord tf
                      on tf.productid=tdt.productid
                     and tf.state = 1
                     and tf.followdate = tdt.resourcedate
         where route.rowstate <> 0
           and route.routetype=3
           and route.taskorderno = R.taskorderno)as 预计出发时间,
       (select min(to_char(nvl(tf.realstarttime,(select tfr.realleavertime
                                                   from YLHT.tb_base_truckfollowrecord tfr
                                                  where tfr.state=1
                                                    and tfr.scheduleorderno = trd.buyscheduleorder
                                                    and tfr.routeorder =(select min(tfr2.routeorder)
                                                                           from YLHT.tb_base_truckfollowrecord tfr2
                                                                          where tfr2.state=1
                                                                            and tfr2.scheduleorderno =tfr.scheduleorderno)
                                                                            and rownum=1)),
                         ''yyyy-mm-dd hh24:mi:ss'')) as realstarttime
          from YLHT.tb_sale_taskorder_routedetail route
               left join YLHT.tb_buy_assemrecord trd
                      on route.taskorderno = trd.taskorderno
                     and route.taskproductid = trd.taskproductid
                     and trd.fromnetpointid = route.taskfromnetpoint
                     and trd.tonetpointid = route.tasktonetpoint
                     and (trd.routeorder = route.routeorder or trd.buyorderdetailid is not null)
                     and trd.state>0
               left join YLHT.tb_buy_buyorder_detail tdt
                      on tdt.buyorderno = trd.buyscheduleorder
                     and tdt.fromnetpointid = route.taskfromnetpoint
                     and tdt.tonetpointid = route.tasktonetpoint
                     and (tdt.taskorderno is null or  tdt.taskorderno=route.taskorderno)
                     and tdt.productid=trd.productid
               left join YLHT.tb_sys_product pt
                      on pt.productid=tdt.productid
                     and pt.transportproduct=1
               left join YLHT.tb_buy_flightfollowrecord tf
                          on tf.productid=tdt.productid
                         and tf.state = 1
                         and tf.followdate = tdt.resourcedate
         where route.rowstate <> 0
           and route.routetype=3
           and route.taskorderno = R.taskorderno) as 实际出发时间,
            (select max(to_char(nvl(tf.planarrivetime,(select tfr.prearrivetime
                                                    from YLHT.tb_base_truckfollowrecord tfr
                                                   where tfr.state=1
                                                     and tfr.scheduleorderno = trd.buyscheduleorder
                                                     and tfr.routeorder =(select max(tfr2.routeorder)
                                                                            from YLHT.tb_base_truckfollowrecord tfr2
                                                                           where tfr2.state=1
                                                                             and tfr.state=1
                                                                             and tfr2.scheduleorderno=tfr.scheduleorderno)
                                                                             and rownum=1)),
                         ''yyyy-mm-dd hh24:mi:ss'') ) as planarrivetime
          from YLHT.tb_sale_taskorder_routedetail route
               left join YLHT.tb_buy_assemrecord trd
                      on route.taskorderno = trd.taskorderno
                     and route.taskproductid = trd.taskproductid
                     and trd.fromnetpointid = route.taskfromnetpoint
                     and trd.tonetpointid = route.tasktonetpoint
                     and (trd.routeorder = route.routeorder or trd.buyorderdetailid is not null)
                     and trd.state>0
               left join YLHT.tb_buy_buyorder_detail tdt
                      on tdt.buyorderno = trd.buyscheduleorder
                     and tdt.fromnetpointid = route.taskfromnetpoint
                     and tdt.tonetpointid = route.tasktonetpoint
                     and (tdt.taskorderno is null or  tdt.taskorderno=route.taskorderno)  and tdt.productid=trd.productid
               left join YLHT.tb_sys_product pt
                      on pt.productid=tdt.productid
                     and pt.transportproduct=1
               left join YLHT.tb_buy_flightfollowrecord tf
                      on tf.productid=tdt.productid
                     and tf.state = 1
                     and tf.followdate = tdt.resourcedate
         where route.rowstate <> 0
           and route.routetype=3
           and route.taskorderno = R.taskorderno) 预计到达时间,
             (select max(to_char(nvl(tf.realarrivetime,(select tfr.realarrivetime
                                                    from YLHT.tb_base_truckfollowrecord tfr
                                                   where tfr.state=1
                                                     and tfr.scheduleorderno = trd.buyscheduleorder
                                                     and tfr.routeorder =(select max(tfr2.routeorder)
                                                                            from YLHT.tb_base_truckfollowrecord tfr2
                                                                           where tfr2.state=1
                                                                             and tfr2.scheduleorderno=tfr.scheduleorderno)
                                                                             and rownum=1)),
                         ''yyyy-mm-dd hh24:mi:ss'') ) as realarrivetime
          from YLHT.tb_sale_taskorder_routedetail route
               left join YLHT.tb_buy_assemrecord trd
                      on route.taskorderno = trd.taskorderno
                     and route.taskproductid = trd.taskproductid
                     and trd.fromnetpointid = route.taskfromnetpoint
                     and trd.tonetpointid = route.tasktonetpoint
                     and (trd.routeorder = route.routeorder or trd.buyorderdetailid is not null)
                     and trd.state>0
               left join YLHT.tb_buy_buyorder_detail tdt
                      on tdt.buyorderno = trd.buyscheduleorder
                     and tdt.fromnetpointid = route.taskfromnetpoint
                     and tdt.tonetpointid = route.tasktonetpoint
                     and (tdt.taskorderno is null or  tdt.taskorderno=route.taskorderno)
                     and tdt.productid=trd.productid
               left join YLHT.tb_sys_product pt
                      on pt.productid=tdt.productid
                     and pt.transportproduct=1
               left join YLHT.tb_buy_flightfollowrecord tf
                      on tf.productid=tdt.productid
                     and tf.state = 1
                     and tf.followdate = tdt.resourcedate
         where route.rowstate <> 0
           and route.routetype=3
           and route.taskorderno =R.taskorderno) as 实际到港时间,
             (SELECT MAX(J.REALFINISHTIME) FROM YLHT.TB_OPE_JOBORDER J
                               INNER JOIN YLHT.TB_OPE_JOBORDER_DETAIL JD
                                  ON JD.JOBORDERNO=J.JOBORDERNO
                                LEFT JOIN YLHT.tb_sys_customer C ON C.CUSTOMERID=J.JOBORG
                               WHERE J.STATE>0
                                 AND J.JOBORDERTYPE=11
                                 AND C.CUSTOMERTYPE IN (12,22)
                                 AND JD.TASKORDERNO=R.TASKORDERNO) 提货完成时间,
       CASE WHEN R.TASKTYPE IN (4,8,9) THEN
                 (SELECT MAX(J.REALFINISHTIME) FROM YLHT.TB_OPE_JOBORDER J
                                              INNER JOIN YLHT.TB_OPE_JOBORDER_DETAIL JD
                                                      ON JD.JOBORDERNO=J.JOBORDERNO
                                              WHERE J.STATE>0
                                                AND J.JOBORDERTYPE=2
                                                AND J.PRODUCTID=5
                                                AND JD.TASKORDERNO=R.TASKORDERNO)
            ELSE
                 (SELECT MAX(J.REALFINISHTIME) FROM YLHT.TB_OPE_JOBORDER J
                                                                INNER JOIN YLHT.TB_OPE_JOBORDER_DETAIL JD
                                                                        ON JD.JOBORDERNO=J.JOBORDERNO
                                                                LEFT JOIN YLHT.tb_sys_customer C ON C.CUSTOMERID=J.JOBORG
                                                                WHERE J.STATE>0
                                                                  AND J.JOBORDERTYPE=12
                                                                  AND C.CUSTOMERTYPE IN (12,22)
                                                                  AND JD.TASKORDERNO=R.TASKORDERNO)
        END AS          派送时间,

         (select sd.siginindate from YLHT.tb_ope_sigininorder sd
                             where sd.taskorderno=R.taskorderno
                               and sd.state = 1
                               and rownum=1) as 实际签收时间,
       (select sd.sigininenteringdate from YLHT.tb_ope_sigininorder sd
                                     where sd.taskorderno =R.taskorderno
                                       and sd.state = 1
                                       and rownum=1)as 签收录入时间,

   (SELECT DECODE(sd.sigininstate,1,''待签收'',''已签收'') from YLHT.tb_ope_sigininorder sd
                                                            where sd.taskorderno =R.taskorderno
                                                              and sd.state = 1
                                                              and rownum=1)as 签收状态,
       (select sd.sigininname from YLHT.tb_ope_sigininorder sd
                         where sd.taskorderno =R.taskorderno
                           and sd.state = 1 and rownum=1) as 签收人,
       decode(c.changetype,1,''基本信息变更'',''退返转变更'')  as 变更类型,
       c.createtime as 申请时间,
       c.updatetime as 审核时间,
       c.confirmtime 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 任务单制单时间,
       (select u2.username  from YLHT.tb_sys_customer_permission p1
                                 inner join YLHT.tb_sys_user u2
                                         on p1.userid = u2.userid
                                        and p1.permissiontype = 2
                                        and p1.state=1
                           where = p1.customerid = R.customerid
                             and p1.state>0
                             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

这个才是全部，上面有误，希望能有神的帮助

#8

mywisdom882016-09-12 12:57

这么长，建议分段检查。。

#9

awinna2016-09-14 21:10

回复 8楼 mywisdom88

要是系统各位大神能直接调用，肯定能细细教我，两位版主说的分段测试是唯一办法了，好在做不出来，领导也不说什么，谢谢哈

#10

mywisdom882016-09-15 14:29

回复 9楼 awinna

你的语句太长了，别人看都费劲，除非很明显的错误。要不很费时间，再说，我们也没环境测试。

#11

awinna2016-09-20 16:23

回复 10楼 mywisdom88

说的是说的是，没环境试，谢谢哈

#12

awinna2016-09-20 16:26

程序代码：

(SELECT decode(sod.sigininstate,1,''待签收'',''已签收'') from YLHT.tb_ope_sigininorder sod
                                                            where sod.taskorderno =R.taskorderno
                                                              and sod.state = 1
                                                              and rownum=1)as 签收状态,
这句运行不了
(SELECT sod.sigininstate from YLHT.tb_ope_sigininorder sod
                                                            where sod.taskorderno =R.taskorderno
                                                              and sod.state = 1
                                                              and rownum=1)as 签收状态,
这样可以运行
第一段改怎样修改呢？谢谢哈

#13

mywisdom882016-09-20 17:44

(SELECT sod.sigininstate from YLHT.tb_ope_sigininorder sod
where sod.taskorderno =R.taskorderno and sod.state = 1 and rownum=1)as 签收状态,
在SQL的数据库中，格式是 [数据库名称].[拥有者].[表名],你现在用的是 YLHT.tb_ope_sigininorder，如果你语法没问题的话，你用 dbo.tb_ope_sigininorder看看，
你的是Oracle数据库，不知道是不是这样格式的，如 [master].[dbo].[表名]
你可以单独运行这个句，看看
SELECT sod.sigininstate from YLHT.tb_ope_sigininorder sod
where sod.taskorderno =R.taskorderno and sod.state = 1 and rownum=1
--R.taskorderno 改为你的值。来测试看看

#14

awinna2016-09-20 19:05

回复 13楼 mywisdom88

(SELECT sod.sigininstate from YLHT.tb_ope_sigininorder sod
                                                             where sod.taskorderno =R.taskorderno
                                                               and sod.state = 1
                                                               and rownum=1)as 签收状态,
运行结果如下
签收录入时间    签收状态    任务单制单时间
34:02.0    2    16:59.0
52:54.0    2    31:25.0
46:26.0    2    17:44.0
02:04.0    2    46:29.0
NULL    NULL    12:50.0
(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函数后就出错了，不知该如何

#15

mywisdom882016-09-20 22:12

decode(sod.sigininstate,1,''待签收'',''已签收'')
我不知道你这里是做什么用的。你不是想得到
'待签收'或者'已签收'
在sql2000中，你想输出带引号的时候，1个引号要用2个引号代替。如
select '''你好'''
输出得到的是 '你好'
set @变量='你好'
得到的是你好
set @变量=''你好'' --是错误的
set @变量='''你好'''
得到的是 '你好'

[此贴子已经被作者于2016-9-20 22:14编辑过]

#16

awinna2016-09-20 22:31

回复 15楼 mywisdom88

我只想要结果显示成，待签收或已签收。代码中除了as后面的汉字，都要'''',才能正常。用的sql server 2012,看到都是加一个'',不知为何。至于您说的oracle,小菜鸟我也是不明白怎样转去的

#17

mywisdom882016-09-21 13:40

链接服务器"YLHT"的 OLE DB 访问接口 "OraOLEDB.Oracle" 返回了消息 "ORA-00904: "OS"."STATE": 标识符无效"。
我以为你的是Oracle数据库。。
你的是SQL2012，你加了这个就出错误，decode(sod.sigininstate,1,''待签收'',''已签收'')，你这个函数decode()问题？你把 ''待签收'' 改为 '''待签收''',用3个引号，不要用2个。。。。

#18

awinna2016-09-22 09:30

回复 17楼 mywisdom88

把 ''待签收'' 改为 '''待签收''',加上3个''，代码会出现波浪线，提示出错，运行也出错。
无论是
(SELECT DECODE(sod.sigininstate,1,''待签收'',''已签收'') from YLHT.tb_ope_sigininorder sod
                                                            where sod.taskorderno =R.taskorderno
                                                              and sod.state = 1
                                                              and rownum=1)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 母子单

#19

awinna2016-09-23 10:40

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

#20

awinna2016-09-23 10:42

错误提示为
消息 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' 无效。

#21

awinna2016-09-29 09:22

(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 签收录入时间,

      (SELECT DECODE(sod.sigininstate,1,''待签收'',''已签收'') from YLHT.tb_ope_sigininorder sod
                                                            where sod.taskorderno =R.taskorderno
                                                              and sod.state = 1
                                                              and rownum=1)as 签收状态,
最新测试是
这三个字段任意选两个都可以运行，如果全选就会出错，求解，谢谢

#22

awinna2016-09-29 09:31

回复 21楼 awinna

减少缩进，就可以了，真是涨见识了

#23

mywisdom882016-09-29 12:36

以下是引用awinna在2016-9-29 09:31:35的发言：

减少缩进，就可以了，真是涨见识了

恭喜啊，本来，就不需要缩进这么厉害。

#24

厨师王德榜2016-09-29 12:54

你还别说，这么大段的 Select ... 语句，看着就过瘾，挺有高手范儿的。

#25

awinna2016-09-29 14:00

回复 24楼厨师王德榜

代码是很久以前未见过的同事写的，只是现在换了一个链接端口，链接的物流系统中的表内容不同但表的格式是一样的。
现在的问题更麻烦了，目测19楼只是中的代码差不多只是全部代码中的一半，剩下的寸步难行，伤不起