Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 2 of 2 total |
Problem using EXCEPT clause in SELECT |
Sun, Jul 9 2006 12:49 PM | Permanent Link |
Phil | I'm trying to get a SELECT statement working, using EXCEPT to combine two SELECT statements.
My code is as follows: SELECT a.HEADINGID, A.HEADINGDESC FROM ALColumn a INNER JOIN ALType b on a.TYPEID = b.TYPEID WHERE b.TYPE = 'LISTS' EXCEPT SELECT c.HEADINGID, CAST('' As Char(20)) FROM AlTime c INNER JOIN ALType b on c.TYPEID = b.TYPEID WHERE b.TYPE = 'LISTS' and c.DAYOFWEEK = 3 AND c.OPENTIME = '00:00:00' AND c.CLOSETIME = '00:00:00' So the first part of the statement should return recs A and recs B and the second part should return just rec B, so my result set (if the EXCEPT works correctly) should be just rec A. However, I'm getting both recs A and B as my result. If I change the statement so I'm not returning a.HEADINGDESC in my results, and therefore I also remove the CAST part of the second statement, it all works fine and just rec A is returned, however I could do with having the a.HEADINGDESC in my results. I need the CAST as there is not a HEADINGDESC field in the ALTime table and EXCEPT requires the same number and the same type of fields in both SELECTS. Anyone have any ideas? Thanks, Phil. |
Mon, Jul 10 2006 1:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Phil,
<< If I change the statement so I'm not returning a.HEADINGDESC in my results, and therefore I also remove the CAST part of the second statement, it all works fine and just rec A is returned, however I could do with having the a.HEADINGDESC in my results. I need the CAST as there is not a HEADINGDESC field in the ALTime table and EXCEPT requires the same number and the same type of fields in both SELECTS. >> So, I assume that none of the records in the first SELECT have a blank HEADINGDESC column ? If so, then what you need is an EXCEPT that allows specification of the columns to use for the EXCEPT. However, DBISAM doesn't support that at this time, so you'll have to do a work-around by using this SQL instead: SELECT a.HEADINGID, A.HEADINGDESC FROM ALColumn a INNER JOIN ALType b on a.TYPEID = b.TYPEID WHERE b.TYPE = 'LISTS' EXCEPT SELECT c.HEADINGID, a.HEADINGDESC FROM AlTime c INNER JOIN ALType b on c.TYPEID = b.TYPEID INNER JOIN AlColumn a on a.HEADINGID=c.HEADINGID WHERE b.TYPE = 'LISTS' and c.DAYOFWEEK = 3 AND c.OPENTIME = '00:00:00' AND c.CLOSETIME = '00:00:00' That should give you the same HEADINGDESC as used in the first query, thus allowing the EXCEPT to work okay. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |