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.

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