10-01-12, 16:00 #1Registered User
- Join Date
- Oct 2012
Unanswered: Invalid column name message in the query
Hi All, I am receiving the invalid column name message in the subquery. Please help me if there is any work around or change in the query:
update table1 set table1.column1 =
(Select column1 from
(Select column1 from table2 where
table1.columnX = table2.columnX
order by table2.columnM desc,table2.ColumnN desc,
where rownum =1)
When i run the above query i am receiving the following error message:
ORA-00904:invalid column name at table1.columnx.
This column is already existing in table1 and looks like this table is not recoginzed inside of the subquery.
I am trying to update table1.column1 using the first value from table2 after ordering by table2.columnM desc, table2.columnN desc. (ColumnM and ColumnN are the Date columns which i need to perform the sorting). I am new to sql/Plsql. Thanks in Advance for help....
Actualy query I am running:
UPDATE MBRSHP_WK2 member set member.RATE_HEALTH =
VMMBU.BILL_RATE_PURE_AM from (select VMMBU.BILL_RATE_PURE_AM
FROM IMMP.VMBBU_MBSH_BU_DUPD VMMBU,ICPC.VCNOP_CNTR_OPTN VCNOP, IBPB.VPROD_PRODUCT VPROD
member.FK_ENRU_PURCH_IK = VMMBU.FK_MBCO_PURCH_IK
AND member.FK_ENRU_ENRL_NB = VMMBU.FK_MBCO_ENRL_NB
AND member.SCB_PER_IK = VMMBU.FK_MBCO_SCB_PER_IK
AND member.SCB_SEQ_NB = VMMBU.FK_MBCO_SCB_SEQ_NB
AND member.PRSN_IK = VMMBU.FK_MBCO_PRSN_IK
AND member.MBRSHP_SEQ_NB = VMMBU.FK_MBCO_MBSHSEQ_NB
AND VMMBU.FK_MBCO_CNT_OPT_IK = VCNOP.CNTR_OPTN_IK
AND VCNOP.FK_PROD_SHRT_NM = VPROD.SHRT_NM
AND VPROD.CAT_CD = 'NON MEDICARE'
order by VMMBU.BU_CVRG_EFF_DT desc,
10-02-12, 11:24 #2Registered User
- Join Date
- Mar 2007
the reason for the error is, that you table from the outer query may be referenced in correlated subquery only one level down.
So, instead of classical TOP N query, you should use appropriate aggregate function. Here, DENSE_RANK seems to be the right one. It is described in detail in SQL Language Reference book, which is available with other documentation e.g. online on http://tahiti.oracle.com/
As you did not post a test case - DDL statements (CREATE TABLE) for used tables, DML statements (INSERT) for sample data - here is just a query demonstrating its usage:
-- Sample data; as you did not post yours, I had to make up some with table1 as ( select 'A' column1, 1 columnx from dual union all select 'B' column1, 2 columnx from dual ), table2 as ( select 'X' column1, 1 columnx, 3 columnm, 2 columnn from dual union all select 'Y' column1, 1 columnx, 3 columnm, 4 columnn from dual union all select 'Z' column1, 1 columnx, 2 columnm, 5 columnn from dual union all select 'R' column1, 2 columnx, 1 columnm, 1 columnn from dual ) -- Respective query obtaining required value from given tables select table1.columnx, max( table2.column1 ) keep ( dense_rank first order by table2.columnM desc, table2.ColumnN desc ) column1 from table1, table2 where table1.columnX = table2.columnX group by table1.columnx;