Datawindow查询不尊重ORDER子句

Question

我正在开发一个表格，以便有一个采购订单。

我的主窗体中有一个作为报表对象的Datawindow。此数据窗口(子报表)有一个select来检索该订单的订单行。除了ORDER BY子句之外，一切正常。我需要按订单行号(orderline.orderlineno)按降序顺序对订单行排序。

我已经通过Sybase Central > Interactive尝试了SQL语句，它以所需的顺序显示记录，但在我的应用程序中使用该表单时，它将显示按orderline.forcompid排序的记录。很奇怪

我的SQL语句或Power中的其他东西中是否有我遗漏的错误？

数据窗口(子报表) SQL

( SELECT orderline.orderlineno,   
         orderline.name,   
         orderline.unitid,   
         orderline.quantity,   
         orderline.makerref,   
         orderline.price, 
orderline.discount , 
         orderline.currencycode,
         orderline.linecontent,
         orderline.workorderid,
         componenttype.compname,   
         componenttype.comptype,   
         componentunit.compno,   
         componentunit.serialno,   
         workorder.compjobid,
         workorder.title, 
            workorder.woorigin
,orderline.notes

    FROM orderline,   
         componenttype,   
         componentunit,   
         workorder  
   WHERE ( componentunit.comptypeid = componenttype.comptypeid ) and  
         ( componentunit.compid = orderline.forcompid ) and  
         ( orderline.workorderid = workorder.workorderid ) and 
         ( orderline.orderid = :ll_OrderID ) AND  
         ( orderline.status = 1 ) AND
         ( orderline.includeonform <> 0 ) 
union
  SELECT orderline.orderlineno,   
         orderline.name,   
         orderline.unitid,   
         orderline.quantity,   
         orderline.makerref,   
         orderline.price,   
orderline.discount ,
         orderline.currencycode,
         orderline.linecontent,
         orderline.workorderid,
         componenttype.compname,   
         componenttype.comptype,   
         componentunit.compno,   
         componentunit.serialno,    
         0,
         NULL,
         0 
,orderline.notes

    FROM orderline,   
         componenttype,   
         componentunit   
   WHERE orderline.workorderid IS NULL AND
         ( componentunit.comptypeid = componenttype.comptypeid ) and  
         ( componentunit.compid = orderline.forcompid ) and  
         ( orderline.orderid = :ll_OrderID ) AND  
         ( orderline.status = 1 ) AND
         ( orderline.includeonform <> 0 ) 

union
  SELECT orderline.orderlineno,   
         orderline.name,   
         orderline.unitid,   
         orderline.quantity,   
         orderline.makerref,   
         orderline.price,   
orderline.discount ,
         orderline.currencycode,
         orderline.linecontent,   
         orderline.workorderid,
         NULL,   
         NULL, 
         NULL, 
         NULL,  
         workorder.compjobid,
         workorder.title,
            workorder.woorigin 
,orderline.notes
    FROM orderline, 
         workorder  

    WHERE orderline.forcompid IS NULL AND 
         ( orderline.workorderid = workorder.workorderid ) and 
         ( orderline.orderid = :ll_OrderID ) AND  
         ( orderline.status = 1 ) AND 
         ( orderline.includeonform <> 0 ) 

union
  SELECT orderline.orderlineno,   
         orderline.name,   
         orderline.unitid,   
         orderline.quantity,   
         orderline.makerref,   
         orderline.price, 
orderline.discount ,  
         orderline.currencycode,
         orderline.linecontent, 
         orderline.workorderid,  
         NULL,   
         NULL, 
         NULL, 
         NULL, 
         0, 
         NULL, 
         0   
,orderline.notes
    FROM orderline

    WHERE orderline.forcompid IS NULL AND 
         ( orderline.workorderid IS NULL ) and 
         ( orderline.orderid = :ll_OrderID ) AND  
         ( orderline.status = 1 ) AND 
         ( orderline.includeonform <> 0 ) 
) 
ORDER BY 1 ASC

**编辑:修改后的SQL **

SELECT orderline.orderlineno,
 orderline.name,   
         orderline.unitid,   
         orderline.quantity,   
         orderline.makerref,   
         orderline.price, 
         orderline.discount, 
         orderline.currencycode,
         orderline.linecontent,
         orderline.workorderid,
         componenttype.compname,   
         componenttype.comptype,   
         componentunit.compno,   
         componentunit.serialno,   
         workorder.compjobid,
         workorder.title, 
     workorder.woorigin,
         orderline.notes

FROM

( SELECT 
         A.orderlineno AS LineNo,   
         A.name as LineName ,   
         A.unitid AS Unit,   
         A.quantity As Qty,   
         A.makerref AS Maker,   
         A.price AS Price, 
         A.discount As Dsc, 
         A.currencycode As Curr,
         A.linecontent As content,
         A.workorderid AS WOID,
         B.compname AS CName,   
         B.comptype As CType,   
         C.compno AS CNo,   
         C.serialno As Serial,   
         D.compjobid AS CJob,
         D.title As Tit, 
         D.woorigin AS WOor,
         A.notes As Nots

    FROM orderline A,   
         componenttype B,   
         componentunit C,   
         workorder D 
   WHERE ( C.comptypeid = B.comptypeid ) and  
         ( C.compid = A.forcompid ) and  
         ( A.workorderid = D.workorderid ) and 
         ( A.orderid = 40003774 ) AND  
         ( A.status = 1 ) AND
         ( A.includeonform <> 0 ) 
union
  SELECT 
         A1.orderlineno AS LineNo,   
         A1.name as LineName ,   
         A1.unitid AS Unit,   
         A1.quantity As Qty,   
         A1.makerref AS Maker,   
         A1.price AS Price, 
         A1.discount As Dsc, 
         A1.currencycode As Curr,
         A1.linecontent As content,
         A1.workorderid AS WOID,
         B1.compname AS CName,   
         B1.comptype As CType,   
         C1.compno AS CNo,   
         C1.serialno As Serial,   
         0,
         NULL, 
         0,
        A1.notes As Nots

    FROM orderline A1,   
         componenttype B1,   
         componentunit C1  
   WHERE A1.workorderid IS NULL AND
         ( C1.comptypeid = B1.comptypeid ) and  
         ( C1.compid = A1.forcompid ) and  
         ( A1.orderid = 40003774 ) AND  
         ( A1.status = 1 ) AND
         ( A1.includeonform <> 0 ) 

union
  SELECT 

         A2.orderlineno AS LineNo,   
         A2.name as LineName ,   
         A2.unitid AS Unit,   
         A2.quantity As Qty,   
         A2.makerref AS Maker,   
         A2.price AS Price, 
         A2.discount As Dsc, 
         A2.currencycode As Curr,
         A2.linecontent As content,
         A2.workorderid AS WOID,
         NULL,  
         NULL,  
         NULL,   
         NULL,   
         B2.compjobid AS CJob,
         B2.title As Tit, 
         B2.woorigin AS WOor,
         A2.notes As Nots


    FROM orderline A2, 
         workorder B2  

    WHERE A2.forcompid IS NULL AND 
         ( A2.workorderid = B2.workorderid ) and 
         ( A2.orderid = 40003774 ) AND  
         ( A2.status = 1 ) AND 
         ( A2.includeonform <> 0 ) 

union
  SELECT 

         A3.orderlineno AS LineNo,   
         A3.name as LineName ,   
         A3.unitid AS Unit,   
         A3.quantity As Qty,   
         A3.makerref AS Maker,   
         A3.price AS Price, 
         A3.discount As Dsc, 
         A3.currencycode As Curr,
         A3.linecontent As content,
         A3.workorderid AS WOID,
         NULL,   
         NULL,   
         NULL,   
         NULL,  
         0,
         NULL, 
         0,
         A3.notes As Nots

    FROM orderline A3

    WHERE A3.forcompid IS NULL AND 
         ( A3.workorderid IS NULL ) and 
         ( A3.orderid = 40003774 ) AND  
         ( A3.status = 1 ) AND 
         ( A3.includeonform <> 0 ) 

)
         orderline,   
         componenttype,   
         componentunit,   
         workorder

ORDER BY 1 ASC

Answer 1

跟着这一步..。

1. 首先，为内部的列提供别名，选择statements..since，它的联合，别名应该在所有查询中匹配。
2. 用别名放置外部select，并使用所需列的别名进行排序。

**请记住，订单将影响执行时间。

看下面的样本

SELECT  LineNo,
        LineName,   
        Unit,   
        QTY,   
         .......

FROM

( SELECT 
         A.orderlineno AS LineNo,   
         A.name as LineName ,   
         A.unitid AS Unit,   
         A.quantity As Qty,   
         ...........

    FROM orderline A,   
         componenttype B,   
         componentunit C,   
         workorder D 
   WHERE ( C.comptypeid = B.comptypeid ) and  
         ( C.compid = A.forcompid ) and  
         ( A.workorderid = D.workorderid ) and 
         ( A.orderid = 40003774 ) AND  
         ( A.status = 1 ) AND
         ( A.includeonform <> 0 ) 
union
  SELECT 
         A1.orderlineno AS LineNo,   
         A1.name as LineName ,   
         A1.unitid AS Unit,   
         A1.quantity As Qty,   
        .........

    FROM orderline A1,   
         componenttype B1,   
         componentunit C1  
   WHERE A1.workorderid IS NULL AND
         ( C1.comptypeid = B1.comptypeid ) and  
         ( C1.compid = A1.forcompid ) and  
         ( A1.orderid = 40003774 ) AND  
         ( A1.status = 1 ) AND
         ( A1.includeonform <> 0 ) 

union
  ........
) AS t1

ORDER BY LineNo ASC

Answer 2

您的SQL很好。最好是取消工会的订单。在数据窗口本身中设置排序顺序。拖动要按顺序排序的列到右侧，并取消选中“升格”框。这总是覆盖SQL排序。