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