RE: SQL Join of intermediate tables and failing ORDER BY

[darren@xxxxxxxxx]

I had thought of it and no dice.  Numeric ORDER BY does not seem to make a
difference.  Sorry, I should have
said what I'd already tried.  I also tried commenting out the summation
(CFTLVL+CFPLVL), and this did not seem to affect the overall sort.





>With UNIONs, I would change my ORDER BY clause to use the numeric
placement
>of the field you want to order by - in your example below - "ORDER BY 3".

>Dave


SQL statement:

DECLARE CURS1 CURSOR FOR
 WITH BTAB AS
   (SELECT *
      FROM ITHL01 LEFT OUTER JOIN CMF2L01
      ON CFPROD=TPROD AND CFFAC=:WMFAC
      WHERE TTYPE='B' AND
        TWHS='02' AND
        TTDTE>=:FromDate AND TTDTE<=:ToDate),
 HTAB AS
    (SELECT *
      FROM ITHL01 LEFT OUTER JOIN CMF2L01
      ON CFPROD=TPROD AND CFFAC=:WMFAC
      WHERE TTYPE='H' AND
        TWHS<>'02' AND
        TTDTE>=:FromDate AND TTDTE<=:ToDate)
 SELECT F1.TTYPE, F1.TWHS, F1.TPROD, F1.TQTY, F1.TSCST, F1.TTDTE
   FROM BTAB F1 EXCEPTION JOIN HTAB F2
   ON F1.TPROD=F2.TPROD AND
     F1.TQTY=F2.TQTY*(-1) AND
     F1.TSCST=F2.TSCST AND
     (F1.CFTLVL+F1.CFPLVL)=(F2.CFTLVL+F2.CFPLVL)
 UNION
 SELECT F3.TTYPE, F3.TWHS, F3.TPROD, F3.TQTY, F3.TSCST, F3.TTDTE
   FROM HTAB F3 EXCEPTION JOIN BTAB F4
   ON F3.TPROD=F4.TPROD AND
     F3.TQTY=F4.TQTY*(-1) AND
     F3.TSCST=F4.TSCST AND
     (F3.CFTLVL+F3.CFPLVL)=(F4.CFTLVL+F4.CFPLVL)
 ORDER BY TPROD




_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.