Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    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:
    Sample 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 =
    ( SELECT
    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
    WHERE
    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,
    VMMBU.BU_CVRG_EFF_END_DT desc,
    VMMBU.OVRD_DT asc
    )
    where rownum=1)

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    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:
    Code:
    -- 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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •