Thanks for your prompt reply.

Version 8.1.7
Server OS Windows/2003
Client OS Windows/2000
Client Sql*Plus 8.1.7

I am getting ORA-00904 for a simple query.
But when use it as the sub query, I do not get the error !

The SQL*Plus Spool file is pasted below with some lines removed (but no other editing of course).

11:57:20 vt@mig >desc AIMS_Audit_Mgrs
Name Null? Type
------------------------------- -------- ----
FIRM_NO NOT NULL NUMBER(38)
PARTNER_NO NOT NULL NUMBER(38)
MGR_NO NOT NULL NUMBER(38)
MGR_NAME NOT NULL VARCHAR2(30)
MGR_PHONE VARCHAR2(40)
MGR_MAIL VARCHAR2(100)
STATUS_CD VARCHAR2(1)

11:57:29 vt@mig >desc AIMS_Audit_Sites
Name Null? Type
------------------------------- -------- ----
SITE_NO NOT NULL NUMBER(38)
OFFICE_NO NOT NULL NUMBER(38)
COMP_NO NOT NULL NUMBER(38)
AREA_NO NOT NULL NUMBER(38)
SITE_ABB NOT NULL VARCHAR2(10)

11:57:40 vt@mig >@InD11
11:57:46 vt@mig >column table_name format A20
11:57:46 vt@mig >column index_name format A20
11:57:46 vt@mig >column index_type format A20
11:57:46 vt@mig >column column_name format A20
11:57:46 vt@mig >break on table_name skip on index_name skip
11:57:46 vt@mig >
11:57:46 vt@mig >SELECT I.Table_Name Table_Name, I.Index_Name Index_Name, Uniqueness, Index_Type,
11:57:46 2 Column_name
11:57:46 3 FROM user_indexes I, user_ind_columns C
11:57:46 4 WHERE I.index_name = C.index_name (+)
11:57:46 5 AND I.Table_name LIKE upper ('&Tbl_Nam')
11:57:46 6 ORDER BY I.Table_name, I.Index_Name, C.Column_Position
11:57:46 7 Input truncated to 1 characters
/

Enter value for tbl_nam: AIMS_Audit%
old 5: AND I.Table_name LIKE upper ('&Tbl_Nam')
new 5: AND I.Table_name LIKE upper ('AIMS_Audit%')

TABLE_NAME INDEX_NAME UNIQUENES INDEX_TYPE
-------------------- -------------------- --------- --------------------
COLUMN_NAME
--------------------
AIMS_AUDIT_MGRS AIMS_AUDIT_MGRS_PK UNIQUE IOT - TOP
MGR_NO

AIMS_AUDIT_SITES AIMS_AUDIT_SITES_PK UNIQUE IOT - TOP
SITE_NO

AIMS_AUDIT_SITES_UK1 UNIQUE NORMAL
COMP_NO

AIMS_AUDIT_SITES AIMS_AUDIT_SITES_UK1 UNIQUE NORMAL
AREA_NO

AIMS_AUDIT_SITES_UK2 UNIQUE NORMAL
SITE_ABB

34 rows selected.

<other tables and indexes deleted>

real: 531
11:58:17 vt@mig >get 0.sql
1 SELECT Mgr_No, Mgr_Name FROM AIMS_Audit_Mgrs
2 WHERE Partner_No
3* = (SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1)
11:58:47 vt@mig >r
1 SELECT Mgr_No, Mgr_Name FROM AIMS_Audit_Mgrs
2 WHERE Partner_No
3* = (SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1)

MGR_NO MGR_NAME
---------- ------------------------------
1 SUNIL
2 ANKUR

real: 15
11:58:48 vt@mig >del 1 2
11:58:52 vt@mig >l
1* = (SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1)
11:58:53 vt@mig >c/= (/
1* SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1)
11:58:56 vt@mig >c/)/
1* SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1
11:58:59 vt@mig >l
1* SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1
11:59:00 vt@mig >r
1* SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1
SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1
*
ERROR at line 1:
ORA-00904: invalid column name

real: 15
11:59:02 vt@mig >SELECT Name, Value FROM v$Parameter WHERE value LIKE '8.1.%';

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
compatible
8.1.7

optimizer_features_enable
8.1.7


real: 62
11:59:03 vt@mig >
12:12:52 vt@mig >spo off

12:52:04 vt@mig >set heading on echo off verify off termout on

USER UID TERMINAL ENTRYID
------------------------------ ---------- ---------------- ----------
SESSIONID DATE_AND_TIME
------------- -----------------
VT 44 PC6093 0
51,562 15/12/06 12:51:55

real: 16

real: 15
12:52:04 vt@mig >Input truncated to 14 characters
set termout on
12:52:04 vt@mig >get 0
1* select * from v$Parameter
12:52:06 vt@mig >SELECT Mgr_No, Mgr_Name FROM AIMS_Audit_Mgrs
12:52:12 2 WHERE Partner_No
12:52:12 3 = (SELECT Partner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1)
12:52:12 4 /

MGR_NO MGR_NAME
---------- ------------------------------
1 SUNIL
2 ANKUR

real: 16
12:52:14 vt@mig >ed
Wrote file 0.sql

1 SELECT Mgr_No, Mgr_Name FROM AIMS_Audit_Mgrs
2 WHERE Partner_No
3* = (SELECT xPartner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1)
12:52:21 vt@mig >/
= (SELECT xPartner_No FROM AIMS_Audit_Sites WHERE Comp_No = 1 AND Area_No = 1)
*
ERROR at line 3:
ORA-00904: invalid column name


real: 0
12:52:22 vt@mig >cl scr
12:52:53 vt@mig >spo off

ops$tkyte%ORA9IR2> create table t ( x varchar2(20) );

Table created.

ops$tkyte%ORA9IR2> insert into t values ( 'x' );

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select dummy from dual where dummy in (select dummy from t);

D
-
X

ops$tkyte%ORA9IR2> select dual.dummy from dual where dual.dummy in (select dual.dummy from t);

D
-
X



you just coded a correlated subquery - if you fully qualify your column references it would be more clear.


 

Learned a new point !
Thnaks.

Hello Tom, i have the following MERGE statement with tkprof output
--------------------------------------------------------
TKPROF: Release 10.2.0.3.0 - Production on Wed Nov 07 08:44:08 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Trace file: rmsdev01_ora_423244.trc
Sort options: exeela
***********************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
***********************************************************
SELECT 1 FROM DUAL WHERE TARGET.RPM_PROMO_COMP_DETAIL_ID = SOURCE.PRICE_EVENT_I

Error encountered: ORA-00904
***********************************************************
MERGE INTO RPM_PROMO_COMP_DETAIL TARGET USING (SELECT PRICE_EVENT_ID FROM
RPM_BULK_CC_PE_THREAD RPT, RPM_BULK_CC_PE_SEQUENCE RPS WHERE
RPS.BULK_CC_PE_ID = :B3 AND RPT.BULK_CC_PE_ID = :B3 AND (:B2 = :B1 OR
RPS.STATUS IN ('I', 'E')) AND RPT.THREAD_NUMBER = RPS.THREAD_NUMBER AND
RPT.PARENT_THREAD_NUMBER = RPS.SEQUENCE_NUMBER) SOURCE ON
(TARGET.RPM_PROMO_COMP_DETAIL_ID = SOURCE.PRICE_EVENT_ID) WHEN MATCHED THEN
UPDATE SET STATE = :B2 WHEN NOT MATCHED THEN INSERT
(RPM_PROMO_COMP_DETAIL_ID) VALUES ('abcd')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 52 6 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 52 6 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE RPM_PROMO_COMP_DETAIL (cr=52 pr=0 pw=0 time=621 us)
1 VIEW (cr=52 pr=0 pw=0 time=513 us)
1 NESTED LOOPS OUTER (cr=52 pr=0 pw=0 time=510 us)
1 NESTED LOOPS (cr=49 pr=0 pw=0 time=475 us)
1 TABLE ACCESS FULL RPM_BULK_CC_PE_THREAD (cr=47 pr=0 pw=0 time=437 us)
1 TABLE ACCESS BY INDEX ROWID RPM_BULK_CC_PE_SEQUENCE (cr=2 pr=0 pw=0 time=34 us)
1 INDEX RANGE SCAN RPM_BULK_CC_PE_SEQUENCE_I1 (cr=1 pr=0 pw=0 time=19 us)(object id 173319)
1 TABLE ACCESS BY INDEX ROWID RPM_PROMO_COMP_DETAIL (cr=3 pr=0 pw=0 time=29 us)
1 INDEX UNIQUE SCAN PK_RPM_PROMO_COMP_DETAIL (cr=2 pr=0 pw=0 time=15 us)(object id 78076)
***********************************************************

How can i find the cause of the error :ORA-00904 ?

i dont undestand why tkprof is showing a seperate statement
SELECT 1 FROM DUAL WHERE TARGET.RPM_PROMO_COMP_DETAIL_ID = SOURCE.PRICE_EVENT_I

can you please help on this
Thanks
Raghav

i just noticed a Wired thing that i dont understand. can you please explain this if you know

Here a Merge statement that runs perfectly fine. but when you take a tkprof, you can see ORA-00904 error

TKPROF: Release 10.2.0.3.0 - Production on Tue Nov 11 00:33:01 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Trace file: cs0110g_ora_3473714_NILNOV11b.trc
Sort options: exeela fchela prsela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
***********************************************************
The following statement encountered a error during parse:

SELECT 1 FROM DUAL WHERE
TARGET.BULK_CC_PE_ID = SOURCE.BULK_CC_PE_ID AND TARGET.PRICE_EVENT_ID = SOURCE.PRICE_EVENT_ID

Error encountered: ORA-00904
***********************************************************
MERGE INTO RPM_BULK_CC_PE_THREAD TARGET USING
(SELECT
/*+ INDEX (ril RPM_BULK_CC_PE_IL_I2) */
BULK_CC_PE_ID ,
THREAD_NUMBER ,
PRICE_EVENT_ID,
COUNT(*) ITEM_LOC_COUNT
FROM RPM_BULK_CC_PE_IL RIL
WHERE RIL.BULK_CC_PE_ID = 123455656
GROUP BY BULK_CC_PE_ID,
THREAD_NUMBER,
PRICE_EVENT_ID
) SOURCE ON
(
TARGET.BULK_CC_PE_ID = SOURCE.BULK_CC_PE_ID AND TARGET.PRICE_EVENT_ID = SOURCE.PRICE_EVENT_ID
)
WHEN MATCHED THEN
UPDATE
SET THREAD_NUMBER = SOURCE.THREAD_NUMBER,
PRICE_EVENT_TYPE = 'PC' ,
ITEM_LOC_COUNT = SOURCE.ITEM_LOC_COUNT,
STATUS = 'I' WHEN NOT MATCHED THEN
INSERT
(
BULK_CC_PE_ID
)
VALUES
(
'abcd'
)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.08 0 0 0 0
Execute 1 0.00 0.21 3 3 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.30 3 3 0 0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 1340 (VENKATR_RMSDEV120D)

Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE RPM_BULK_CC_PE_THREAD (cr=3 pr=3 pw=0 time=215249 us)
0 VIEW (cr=3 pr=3 pw=0 time=215234 us)
0 HASH JOIN OUTER (cr=3 pr=3 pw=0 time=215232 us)
0 VIEW (cr=3 pr=3 pw=0 time=215113 us)
0 SORT GROUP BY (cr=3 pr=3 pw=0 time=215108 us)
0 INDEX RANGE SCAN RPM_BULK_CC_PE_IL_I2 (cr=3 pr=3 pw=0 time=215077 us)(object id 4119281)
0 TABLE ACCESS FULL RPM_BULK_CC_PE_THREAD (cr=0 pr=0 pw=0 time=0 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ------
db file sequential read 3 0.08 0.21
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.30 0.30
***********************************************************