Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Experts need sql help.
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-LReceived on Thu Dec 21 2000 - 11:12:21 CST
(or the name of mailing list you want to be removed from). You may
![]() |
![]() |