ORA-00604:error occurred at recursive SQL level1 ORA-00918:column ambiguously defined
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-00604:error occurred at recursive SQL level1 ORA-00918:column ambiguously defined

  1. #1
    Join Date
    Mar 2007
    Posts
    1

    ORA-00604:error occurred at recursive SQL level1 ORA-00918:column ambiguously defined

    When i tried to run the below mentioned sql, im getting "ORA-00604: error occurred at recursive SQL level 1ORA-00918: column ambiguously defined"

    SELECT COUNT(*)
    FROM "COGNOS"."COGNOS_PRODUCT_AGG" "COGNOS_PRODUCT_AGG"
    INNER JOIN "COGNOS"."COGNOS_WIS_PRODUCT_TYPE" "COGNOS_WIS_PRODUCT_TYPE"
    ON "COGNOS_PRODUCT_AGG"."PRODUCT_TYPE_ID"="COGNOS_WIS_PRODUCT_TYPE"."PRODUCT_TYPE_ID"
    AND "COGNOS_PRODUCT_AGG"."MEDIUM_KEY_ID"="COGNOS_WIS_PRODUCT_TYPE"."MEDIUM_KEY_ID"
    INNER JOIN "COGNOS"."COGNOS_JRNL_TRANSACTION_AGG" "COGNOS_JRNL_TRANSACTION_AGG"
    ON "COGNOS_JRNL_TRANSACTION_AGG"."PRODUCT_ID"="COGNOS_PRODUCT_AGG"."PRODUCT_ID"
    INNER JOIN "COGNOS"."DW_SALES_MODEL_SYN" "DW_SALES_MODEL_SYN"
    ON "COGNOS_JRNL_TRANSACTION_AGG"."SALES_MODEL_ID"="DW_SALES_MODEL_SYN"."SALES_MODEL_ID"
    INNER JOIN "COGNOS"."COGNOS_JRNL_CUST_CONTRACT_AGG" "COGNOS_JRNL_CUST_CONTRACT_AGG"
    INNER JOIN "COGNOS"."DW_CONTRACT_SYN" "DW_CONTRACT_SYN"
    ON "COGNOS_JRNL_CUST_CONTRACT_AGG"."CONTRACT_ID"="DW_CONTRACT_SYN"."CONTRACT_ID"
    INNER JOIN "COGNOS"."DW_SALES_REP_SYN" "DW_SALES_REP_SYN_INV_ALIAS"
    ON "COGNOS_JRNL_CUST_CONTRACT_AGG"."SALES_REP_ID_TYPE1"="DW_SALES_REP_SYN_INV_ALIAS"."SALES_REP_ID"
    ON "COGNOS_JRNL_TRANSACTION_AGG"."CUSTOMER_CONTRACT_ID"="COGNOS_JRNL_CUST_CONTRACT_AGG"."CUSTOMER_CONTRACT_ID"
    WHERE "COGNOS_JRNL_CUST_CONTRACT_AGG"."PROFILE_YEAR" IN ('2007')
    AND "DW_CONTRACT_SYN"."CONTRACT_NAME" IN ('Enhanced Access License')
    AND "COGNOS_WIS_PRODUCT_TYPE"."WIS_PRODUCT_TYPE_ID" IN (9)
    AND "DW_SALES_REP_SYN_INV_ALIAS"."SALES_REP_NAME" IN ('Dennis St. Rose')
    AND "DW_SALES_MODEL_SYN"."SALES_MODEL_GROUP_DESC" IN ('Subscription')

    But if i remove either one of the last two lines, it works fine. What could be the issue? Can u pls let me know?

  2. #2
    Join Date
    Mar 2006
    Posts
    74
    the problem with writing SQLS and not keeping to some nice coding standard pattern of indentation, or using a query gen tool, is they become very hard to read

    try this:
    Code:
    SELECT 
      COUNT(*) 
    FROM 
      COGNOS_PRODUCT_AGG cpa
      INNER JOIN 
      COGNOS_WIS_PRODUCT_TYPE cwpt
      ON 
        cwpt.PRODUCT_TYPE_ID = cpa.PRODUCT_TYPE_ID AND 
        cwpt.MEDIUM_KEY_ID   = cpa.MEDIUM_KEY_ID AND
        cwpt.WIS_PRODUCT_TYPE_ID = 9
        
      INNER JOIN 
      COGNOS_JRNL_TRANSACTION_AGG cjta 
      ON 
        cjta.PRODUCT_ID = cpa.PRODUCT_ID 
        
      INNER JOIN 
      DW_SALES_MODEL_SYN dsms 
      ON 
        dsms.SALES_MODEL_ID = cjta.SALES_MODEL_ID AND
        dsms.SALES_MODEL_GROUP_DESC = 'Subscription'
         
      INNER JOIN 
      COGNOS_JRNL_CUST_CONTRACT_AGG cjcca
      ON --this ON clause was moved from the end of the SQL to here
        cjcca.CUSTOMER_CONTRACT_ID = cjta.CUSTOMER_CONTRACT_ID AND
        cjcca.PROFILE_YEAR = '2007'
    
       
      INNER JOIN 
      DW_CONTRACT_SYN dcs 
      ON 
        dcs.CONTRACT_ID = cjcca.CONTRACT_ID AND
        dcs.CONTRACT_NAME = 'Enhanced Access License'
         
      INNER JOIN 
      DW_SALES_REP_SYN dsrs 
      ON 
        dsrs.SALES_REP_ID_TYPE1 = cjcca.SALES_REP_ID AND
        dsrs.SALES_REP_NAME = 'Dennis St. Rose'
    IMHO, this is much neater and cleaner. I've moved an ON clause I felt was in the wrong place, and I've moved the constant expressions (why do you use IN for just a single value?) to the join clauses; doing so helps with outer joins more than inner joins.. It has the same effect as the where clause

    I also removed all the aliases of your tables, because you'd only (mostly) alised them to the same name as the table (pointless) and I removed the repeated refrences to the owner (cognos) though if youre running this sql from another owner, they will need to go back. Dont use " " if you can help it; it adds clutter

    Another thing to consider; you seem to join in several tables purely so you can use the descriptive labels as a where restrictor.. Given that descriptive references can change, you should consider using the index fields for these entries instead.. it also obviates the need for joining in extra tables (unless, of course, these descriptive names are groups, there are many rows all having contract name "Enhanced Access Licence" and different IDs..

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    100% in agreement with Cjard.
    Original code was so messy I just give up.
    Lesson learned "if you want people to help you, at least present your issue in an adequate way" ;-)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width