|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.