RE: SQL Join of intermediate tables and failing ORDER BY

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
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

-----Original Message-----
From: darren@dekko.com [mailto:darren@dekko.com]
Sent: Tuesday, January 08, 2002 10:00 AM
To: midrange-l@midrange.com
Subject: SQL Join of intermediate tables and failing ORDER BY


The below SQL statement is designed to define two intermediate tables from
the same file and then run an exception report against the first and the
second, and then the second to the first.  Everything appears to work
correctly except the sort order at the bottom (ORDER BY TPROD).  It seems
that this ORDER BY is being ignored and the report is being run by TTYPE,
TWS, TPROD.  I noticed that this was the same order I had listed fields in
my joined select statement so I changed the order to TPROD, TTYPE, TWHS and
I got the report sorted by TPROD, TTYPE, TWHS which I guess is a good
work-around, but does anyone know whey the ORDER BY clause is being
ignored?


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.