RE: problem with UNION and not in clause

Posted in 2000

Topics: SQL Development & Query Writing, Connectivity: ESQL/C, 4GL & Embedded SQL, Server Administration

Pierre Delandre — Fri, 21 Jul 2000 10:40:25 -0600 — source: Informix-list mailing list archive (1991-1998)

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. ------_=_NextPart_001_01BFF332.5F3364C2 Content-Type: text/plain; charset="iso-8859-1" for this case I use select f1, f2 from tableA a where f3=1 and f4=2 and ( rec_type='CURRENT' or ( rec_type='PREVIOUS' and not exist (select f1 from tableA b where b.f3=a.f3 and b.f4=a.f4 and b.rec_type='CURRENT') ) Pierre Delandre -----Original Message----- From: lora@ragingbull.com [mailto:lora@ragingbull.com] Sent: Thursday, July 20, 2000 4:04 PM To: informix-list@iiug.org Subject: problem with UNION and not in clause 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' ) THANKS GREATLY for any help! Sent via Deja.com http://www.deja.com/ Before you buy. ------_=_NextPart_001_01BFF332.5F3364C2 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: problem with UNION and not in clause</TITLE> </HEAD> <BODY> for this case I use select f1, f2 from tableA a where f3=3D1 and = f4=3D2 and ( rec_type=3D'CURRENT'         or ( = rec_type=3D'PREVIOUS'         =         and not exist = (select f1 from tableA b where b.f3=3Da.f3 and b.f4=3Da.f4 and = b.rec_type=3D'CURRENT')         ) Pierre Delandre -----Original Message----- From: lora@ragingbull.com [<A = HREF=3D"mailto:lora@ragingbull.com">mailto:lora@ragingbull.com</A>]</FON= T> Sent: Thursday, July 20, 2000 4:04 PM To: informix-list@iiug.org Subject: problem with UNION and not in clause I need to construct a query in ESQL/C with cursors that fetches all rows that are of record_type=3DCURRENT and those with record_type=3DPREVIOUS 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=3D1 and = f4=3D2 and rec_type=3D'CURRENT' union select f1, f2 from tableA where f3=3D1 and = f4=3D2 and rec_type=3D'PREVIOUS' and f1, f2 not in (      = <----- error here!! select from tableA where f1, f2 where f3=3D1 and f4=3D2 and = rec_type=3D'CURRENT' ) THANKS GREATLY for any help! Sent via Deja.com <A HREF=3D"http://www.deja.com/"; = TARGET=3D"_blank">http://www.deja.com/</A>; Before you buy. </BODY> </HTML> ------_=_NextPart_001_01BFF332.5F3364C2--

↪ replying to Pierre Delandre

Joe Celko — Sat, 22 Jul 2000 19:08:21 GMT — source: Informix-list mailing list archive (1991-1998)

I would suggest a minor clean up to avoid needless join conditions in the subquery expression: SELECT A1.f1, A1.f2 FROM TableA AS A1 WHERE A1.f3 = 1 AND A1.f4 = 2 AND (A1.rec_type = 'CURRENT' OR (A1.rec_type = 'PREVIOUS' AND NOT EXISTS (SELECT * FROM TableA AS A2 WHERE A2.f3 = 1 AND A2.f4 = 2 AND A2.rec_type = 'CURRENT')); --CELKO-- Joe Celko, SQL and Database Consultant When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated. Sent via Deja.com http://www.deja.com/ Before you buy.

RE: problem with UNION and not in clause

Related threads

Glossary

Your privacy choices