RE: Experts need sql help.

From: Dara Vaughn <dara.vaughn_at_wcom.com>
Date: Thu, 21 Dec 2000 11:12:21 -0600
Message-Id: <10717.125135@fatcity.com>

Manasa,

It seems like you want to compare the DATA in one table to the COLUMN_NAME of the other table, but queries ALWAYS compare DATA. You cannot have the sub-query on the left side in version 7.3.4, but it does work in 8i. Sub-queries ALWAYS evaluate to a data element, NOT to a column name regardless of whether they are on the left side or the right side! Also, since you are using an equality operator, your subquery MUST return only one row or you will get an error.

You ARE getting the correct answer. The data returned by the sub-query
(PFC_CODE) does NOT equal the constant (PFC2). You would return rows if you
changed your constant to = 'PFC_CODE'.

Dara

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Manasa Rao Sent: Wednesday, December 20, 2000 10:14 AM To: Multiple recipients of list ORACLE-L Subject: Experts need sql help.

Hello all,
I have two tables test1 and reporting_org. I have the structure and test values below. When I do the following query it selects no rows. But I do it separately it gives me the correct value. So, question is can I put a select statement within left side of the where clause. I have always done it on the other side, like a sub-query. Folks, please tell me if anyone tried placing a select stmt on the left side of the where clause.
Thanks for all your help.
Manasa.

09:18:11 SQL> SELECT PFC_CODE FROM REPORTING_ORG
09:18:20   2   WHERE  (SELECT COLUMN_NAME
09:18:20   3             FROM TEST1
09:18:20   4            WHERE TEST1.OID = 2) =  'PFC2';

no rows selected

09:03:23 SQL> DESC TEST1;

Name                                      Null?    Type
----------------------------------------- --------
----------------------------
OID                                                NUMBER(3)
TABLE_NAME                                         VARCHAR2(30)
COLUMN_NAME                                        VARCHAR2(30)

09:17:52 SQL> DESC REPORTING_ORG;
Name                                      Null?    Type
----------------------------------------- --------
----------------------------
DIST_CODE                                          VARCHAR2(20)
PFC_CODE                                           VARCHAR2(20)

09:17:59 SQL> SELECT * FROM TEST1;

       OID TABLE_NAME                     COLUMN_NAME
---------- ------------------------------ ------------------------------
         1 REPORTING_ORG                  DIST_CODE
         2 REPORTING_ORG                  PFC_CODE

09:18:04 SQL> SELECT * FROM REPORTING_ORG; DIST_CODE PFC_CODE

-------------------- --------------------
NC2                  PFC2


_________________________________________________________________

Get your FREE download of MSN Explorer at http://explorer.msn.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Manasa Rao
  INET: rao_manasa_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

Received on Thu Dec 21 2000 - 11:12:21 CST