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"
FROM "COGNOS"."COGNOS_PRODUCT_AGG" "COGNOS_PRODUCT_AGG"
INNER JOIN "COGNOS"."COGNOS_WIS_PRODUCT_TYPE" "COGNOS_WIS_PRODUCT_TYPE"
INNER JOIN "COGNOS"."COGNOS_JRNL_TRANSACTION_AGG" "COGNOS_JRNL_TRANSACTION_AGG"
INNER JOIN "COGNOS"."DW_SALES_MODEL_SYN" "DW_SALES_MODEL_SYN"
INNER JOIN "COGNOS"."COGNOS_JRNL_CUST_CONTRACT_AGG" "COGNOS_JRNL_CUST_CONTRACT_AGG"
INNER JOIN "COGNOS"."DW_CONTRACT_SYN" "DW_CONTRACT_SYN"
INNER JOIN "COGNOS"."DW_SALES_REP_SYN" "DW_SALES_REP_SYN_INV_ALIAS"
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?
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
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
cwpt.PRODUCT_TYPE_ID = cpa.PRODUCT_TYPE_ID AND
cwpt.MEDIUM_KEY_ID = cpa.MEDIUM_KEY_ID AND
cwpt.WIS_PRODUCT_TYPE_ID = 9
cjta.PRODUCT_ID = cpa.PRODUCT_ID
dsms.SALES_MODEL_ID = cjta.SALES_MODEL_ID AND
dsms.SALES_MODEL_GROUP_DESC = 'Subscription'
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'
dcs.CONTRACT_ID = cjcca.CONTRACT_ID AND
dcs.CONTRACT_NAME = 'Enhanced Access License'
dsrs.SALES_REP_ID_TYPE1 = cjcca.SALES_REP_ID AND
dsrs.SALES_REP_NAME = 'Dennis St. Rose'
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..
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.
Click Here to Expand Forum to Full Width