Re: problem with UNION and not in clause

From: lora@ragingbull.com > >I need to construct a query in ESQL/C >with cursors that fetches all rows that >are of record_type=CURRENT and those >with record_type=PREVIOUS only if the CURRENT >for that doesn't exist. Am having trouble with >the "NOT IN" clause. I know if I put a single >field before the NOT IN, it works. How do I construct the query >so that I can put more than 1 field before it? >Basically, I want the subquery in the second select >to exclude all the rows I fetched from the first select. >I'd like to avoid using temp tables since I'm not sure >if I can do that with cursors. > >tableA schema >f1 >f2 >f3 >f4 >record_type > >The key for this table is comprised of f1, f2 and record_type > >I'm trying to do this, but I can't get this >to work in dbaccess. > >select f1, f2 from tableA where f3=1 and f4=2 >and rec_type='CURRENT' >union >select f1, f2 from tableA where f3=1 and f4=2 >and rec_type='PREVIOUS' >and f1, f2 not in ( <----- error here!!>select from tableA where >f1, f2 where f3=1 and f4=2 and rec_type='CURRENT' ) select f1, f2 from tableA where f3=1 and f4=2 and rec_type='CURRENT' union select f1, f2 from tableA where f3=1 and f4=2 and rec_type='PREVIOUS' and f1 not in ( select f1 from tableA where where f3=1 and f4=2 and rec_type='CURRENT' ) ? I think you could also do this with NOT EXISTS... ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com