From: "Kevin Lloyd" Subject: Problems w/compound SQL statement Date: 1998/03/03 Message-ID: <6di6hb$j98$1@ns2.naccess.net>#1/1 X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3 Organization: Naccess Newsgroups: comp.databases.oracle.misc Below is an example of a compound SQL statement that returns 0 rows (Example #3) when using tables "QCLINK" and "SAMPHEADER". Each individual SQL query returns at least one row (Examples 1 &2). Also below, is an example of a compound SQL statement that returns 1 row (Example #4) when using tables "SAMPHEADER" and "SAMPDATA". ************************************************** My questions is this: can anyone think of a reason why the compound select statement using the qclink table won't work ??? ************************************************* 1) Individual SQL statement #1 SQL> SELECT SAMP_JOINID FROM SAMPHEADER WHERE SAMP_JOINID='SJ322977'; SAMP_JOINID -------------------- SJ322977 2) Individual SQL statement #2 SQL> SELECT SAMP_JOINID FROM QCLINK WHERE SAMP_JOINID='SJ322977'; SAMP_JOINID -------------------- SJ322977 SJ322977 SJ322977 SJ322977 SJ322977 3) Compound SQL Statement using QCLINK in () SQL> SELECT SAMP_JOINID FROM SAMPHEADER WHERE SAMP_JOINID IN 2 (SELECT SAMP_JOINID FROM QCLINK WHERE SAMP_JOINID='SJ322977'); no rows selected 4) Compound SQL Statement using SAMPDATA in () SQL> SELECT SAMP_JOINID FROM SAMPHEADER WHERE SAMP_JOINID IN 2* (SELECT SAMP_JOINID FROM SAMPDATA WHERE SAMP_JOINID='SJ322977') SAMP_JOINID -------------------- SJ322977