Datawindow query is not respecting ORDER BY clause

0
votes

I'm developing a form in order to have an order form for procurement purposes.

I've a Datawindow as a report object inside my main form. This datawindow (sub-report) has a select to retrieve the orderlines for that order form. Everything is working fine except the ORDER BY clause. I need to sort the order lines by order line number (orderline.orderlineno) in descending order.

I've tried the SQL statement through Sybase Central > Interactive SQL and it is showing the records in the desired order, but when the form is used in my application it shows the records sorted by orderline.forcompid. It's very weird

Is there any mistake in my SQL statement or another thing in Power Builder that I'm missing?

Datawindow (sub-report) 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

** EDIT: MODIFIED 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
2
sqlsql-order-bysybasepowerbuilderdatawindow
how the outer select looks like - Sachu
@Sachu I've added the Sql statement of the main form. - ɐsɹǝʌ ǝɔıʌ
im asking about the select ...(select... union select...)orderby 1 asc - Sachu
There is no outer select. I just added brackets to encapsulate the SQL prior to apply the ORDER BY clause. Removing the brackets the result is the same as before - ɐsɹǝʌ ǝɔıʌ
do some this like select orderline.orderlineno,....from(select...union select...union)orderby 1 asc give alias to each column in the inside select and use it in outer select..this way i achieve sort in my pgms..dont know whether it will solve ur issue - Sachu

2 Answers

1
votes

Follow the step..

  1. First give alias for the columns inside select statements..since its union the alias should match in all queries.

  2. put an outer select with the alias names and do orderby using the alias of the column u needed.

**Keep in mind that order by will effect the execution time..

look sample below

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
0
votes

Your SQL was fine. Might as well leave off the order by on the union. Set the sort order in the datawindow itself. Drag the column want to sort by over to the right hand side and uncheck the Ascending box. This always overrides SQL sort.