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

[lssaravanan]

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?

[cjard]

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:

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..

[PAVB]

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" ;-)