I have a piece of code which when I run gives me the ORA-00918: column ambiguously defined error
```
WITH t1 AS (
select
PCD.CHANGE_ID PARENT_CHANGE_ID,
ICD.CHANGE_ID INFRA_CHANGE_ID,
ICT.TASK_ID INFRA_TASK_ID,
ICD.SUBMIT_DATE INFRA_CHANGE_SUBMIT_DATE,
ICD.COORDINATOR_GROUP_SUPP_ORG INFRA_CHGE_CRDNTR_GRP_SUPP_ORG,
ICD.COORDINATOR_GROUP INFRA_CHANGE_COORDINATOR_GROUP,
ICD.TEMPLATE INFRA_CHANGE_TEMPLATE,
ICD.LAST_MODIFIED_DATE INFRA_CHANGE_LAST_MODIFIED_DATE,
ICD.CLOSED_DATE INFRA_CHANGE_CLOSED_DATE,
ICD.COMPLETED_DATE INFRA_CHANGE_COMPLETED_DATE,
ICD.ACTUAL_START_DATE INFRA_CHANGE_ACTUAL_START_DATE,
ICD.ACTUAL_END_DATE INFRA_CHANGE_ACTUAL_END_DATE,
ICD.SCHEDULED_START_DATE INFRA_CHANGE_SCHEDULED_START_DATE,
ICD.SCHEDULED_END_DATE INFRA_CHANGE_SCHEDULED_END_DATE,
ICD.STATUS_REASON INFRA_CHANGE_STATUS_REASON,
ICD.STATUS INFRA_CHANGE_STATUS,
ICD.SUMMARY INFRA_CHANGE_SUMMARY,
ICD.OPS_CAT3 INFRA_CHANGE_OPS_CAT3,
ICT.LAST_MODIFIED_DATE INFRA_TASK_LAST_MODIFIED_DATE,
ICT.ACTIVATE_TIME INFRA_TASK_ACTIVATE_TIME,
ICT.ASSIGN_TIME INFRA_TASK_ASSIGN_TIME,
ICT.SUBMIT_DATE INFRA_TASK_SUBMIT_DATE,
ICT.ACTUAL_START_DATE INFRA_TASK_ACTUAL_START_DATE,
ICT.ACTUAL_END_DATE INFRA_TASK_ACTUAL_END_DATE,
ICT.SCHEDULED_START_DATE INFRA_TASK_SCHEDULED_START_DATE,
ICT.SCHEDULED_END_DATE INFRA_TASK_SCHEDULED_END_DATE,
ICT.SEQUENCE INFRA_TASK_SEQUENCE,
ICT.ASSIGNEE_ORGANIZATION INFRA_TASK_ASSIGNEE_ORGANIZATION,
ICT.ASSIGNEE_GROUP INFRA_TASK_ASSIGNEE_GROUP,
ICT.STATUS_REASON INFRA_TASK_STATUS_REASON,
ICT.STATUS INFRA_TASK_STATUS,
ICT.SUMMARY TASK_SUMMARY,
ICT.NAME INFRA_TASK_NAME,
OT.ORG_TOWER ORG_TOWER,
CASE WHEN ICT.SCHEDULED_START_DATE IS NOT NULL AND ICT.SCHEDULED_END_DATE IS NOT NULL THEN
to_char(ROUND(Business_Hours_DUP(cast(from_tz(cast(ICT.SCHEDULED_START_DATE as timestamp), 'GMT') at time zone 'CET' as DATE),
cast(from_tz(cast(ICT.SCHEDULED_END_DATE as timestamp), 'GMT') at time zone 'CET' as DATE),7,18,'Sat/Sun'),2))
END TASK_SCHLD_DURATION_IN_HRS,
CASE WHEN ICT.ACTUAL_START_DATE IS NOT NULL THEN
to_char(ROUND(Business_Hours_DUP(cast(from_tz(cast(ICT.ACTUAL_START_DATE as timestamp), 'GMT') at time zone 'CET' as DATE),
cast(from_tz(cast(SYSDATE as timestamp), 'GMT') at time zone 'CET' as DATE),7,18,'Sat/Sun'),2))
END TASK_CURRNT_DURATION_IN_HRS,
CASE WHEN ICT.SCHEDULED_END_DATE IS NOT NULL THEN
to_char(ROUND(Business_Hours_DUP(cast(from_tz(cast(SYSDATE as timestamp), 'GMT') at time zone 'CET' as DATE),
cast(from_tz(cast(ICT.SCHEDULED_END_DATE as timestamp), 'GMT') at time zone 'CET' as DATE),7,18,'Sat/Sun'),2))
END TASK_OVERDUE_HOURS,
CASE WHEN
cast(from_tz(cast(ICT.SCHEDULED_END_DATE as timestamp), 'GMT') at time zone 'CET' as DATE)>
cast(from_tz(cast(SYSDATE as timestamp), 'GMT') at time zone 'CET' as DATE)
THEN 'YES'
ELSE 'NO'
END SCHLD_END_EXCEEDED,
CASE WHEN ICT.SCHEDULED_START_DATE IS NOT NULL AND ICT.SCHEDULED_END_DATE IS NOT NULL THEN
ROUND(Business_Hours_DUP(cast(from_tz(cast(ICT.SCHEDULED_START_DATE as timestamp), 'GMT') at time zone 'CET' as DATE),
cast(from_tz(cast(ICT.SCHEDULED_END_DATE as timestamp), 'GMT') at time zone 'CET' as DATE),7,18,'Sat/Sun')/11,2)
END TASK_SCHLD_DURATION_IN_DAYS,
CASE WHEN ICT.ACTUAL_START_DATE IS NOT NULL THEN
ROUND(Business_Hours_DUP(cast(from_tz(cast(ICT.ACTUAL_START_DATE as timestamp), 'GMT') at time zone 'CET' as DATE),
cast(from_tz(cast(SYSDATE as timestamp), 'GMT') at time zone 'CET' as DATE),7,18,'Sat/Sun')/11,2)
END TASK_CURRNT_DURATION_IN_DAYS,
CASE WHEN ICT.SCHEDULED_END_DATE IS NOT NULL THEN
ROUND(Business_Hours_DUP(cast(from_tz(cast(SYSDATE as timestamp), 'GMT') at time zone 'CET' as DATE),
cast(from_tz(cast(ICT.SCHEDULED_END_DATE as timestamp), 'GMT') at time zone 'CET' as DATE),7,18,'Sat/Sun')/11,2)
END TASK_OVERDUE_DAYS,
X.COUNT_OF_SERVERS SERVER_COUNT,
ICD.CHANGE_LOCATION CHANGE_LOCATION,
ICD.OPS_CAT3 OPS_CAT3,
RAK_READY_LOCATIONS(ICD.CHANGE_LOCATION) LOCATION,
CASE WHEN ICD.OPS_CAT3='unix-ha-cluster' THEN 'Physical'
WHEN ICD.OPS_CAT3='unix-single' THEN 'Physical'
WHEN ICD.OPS_CAT3='unix-single_san'THEN 'Physical'
WHEN ICD.OPS_CAT3='unix-virtual' THEN 'Virtual'
WHEN ICD.OPS_CAT3='unix-virtual_app-storage' THEN 'Virtual'
WHEN ICD.OPS_CAT3='windows-ha-cluster' THEN 'Physical'
WHEN ICD.OPS_CAT3='windows-single' THEN 'Physical'
WHEN ICD.OPS_CAT3='windows-single_san' THEN 'Physical'
WHEN ICD.OPS_CAT3='windows-vm-single_san' THEN 'Virtual'
WHEN ICD.OPS_CAT3='windows-virtual' THEN 'Virtual'
ELSE 'NA' END AS "SERVER_TYPE",
CASE
WHEN PCD.TEMPLATE LIKE '%unix%' THEN 'Unix'
WHEN PCD.TEMPLATE LIKE '%win%' THEN 'Windows'
ELSE
CASE
WHEN ICD.OPS_CAT3 LIKE 'win%' THEN 'Windows'
WHEN ICD.OPS_CAT3 LIKE 'unix%' THEN 'Unix'
END
END TOWER,
CAST(NULL AS VARCHAR2(200)) AS "OVERDUE BRACKET"
FROM
V_CHANGE_ALL_DATA PCD
JOIN V_CHANGE_TASKS PCT ON PCD.CHANGE_ID=PCT.CHANGE_ID AND PCT.STATUS NOT IN ('Closed','Completed','Cancelled','Staged')
JOIN (
SELECT CR.Request_id,CR.CHANGE_ID, cs.count_of_servers from V_CHANGE_RELATIONS CR
left join (select CHANGE_ID, COUNT(*) COUNT_OF_SERVERS from v_change_relations where
DESCRIPTION LIKE '%os-environment' GROUP BY CHANGE_ID) CS on CS.CHANGE_ID = CR.CHANGE_ID) X ON PCD.CHANGE_ID=X.REQUEST_ID
JOIN V_CHANGE_ALL_DATA ICD ON ICD.CHANGE_ID=X.CHANGE_ID AND ICD.STATUS <> 'Cancelled'
JOIN V_CHANGE_TASKS ICT ON ICT.CHANGE_ID=ICD.CHANGE_ID AND ICT.STATUS NOT IN ('Closed','Staged')
JOIN RAK_TASK_ORG_TOWER OT ON OT.T_ASSIGNEE_GROUP = PCT.ASSIGNEE_GROUP
where
PCD.TEMPLATE LIKE 'slc-asset%provision'
AND ICD.TEMPLATE LIKE '%server-lifecycle:global%'
AND (ICD.STATUS_REASON is null
OR ICD.STATUS_REASON = 'Final Review Complete'
OR ICD.STATUS_REASON = 'Automatically Closed')
AND TIMEZONE_CONVERT(PCD.SCHEDULED_START_DATE) >= to_date('2019-04-29','YYYY-MM-DD')
),
t2 AS (
SELECT PARENT_CHANGE_ID,
INFRA_CHANGE_ID,
INFRA_TASK_ID,
INFRA_CHANGE_SUBMIT_DATE,
INFRA_CHGE_CRDNTR_GRP_SUPP_ORG,
INFRA_CHANGE_COORDINATOR_GROUP,
INFRA_CHANGE_TEMPLATE,
INFRA_CHANGE_LAST_MODIFIED_DATE,
INFRA_CHANGE_CLOSED_DATE,
INFRA_CHANGE_COMPLETED_DATE,
INFRA_TASK_ACTUAL_START_DATE,
INFRA_TASK_ACTUAL_END_DATE,
INFRA_TASK_SCHEDULED_START_DATE,
INFRA_TASK_SCHEDULED_END_DATE,
INFRA_CHANGE_STATUS_REASON,
INFRA_CHANGE_STATUS,
INFRA_CHANGE_SUMMARY,
INFRA_CHANGE_OPS_CAT3
INFRA_TASK_LAST_MODIFIED_DATE,
INFRA_TASK_ACTIVATE_TIME,
INFRA_TASK_ASSIGN_TIME,
INFRA_TASK_SUBMIT_DATE,
INFRA_TASK_ACTUAL_START_DATE,
INFRA_TASK_ACTUAL_END_DATE,
INFRA_TASK_SCHEDULED_START_DATE,
INFRA_TASK_SCHEDULED_END_DATE,
INFRA_TASK_SEQUENCE,
INFRA_TASK_ASSIGNEE_ORGANIZATION,
INFRA_TASK_ASSIGNEE_GROUP,
INFRA_TASK_STATUS_REASON,
INFRA_TASK_STATUS,
TASK_SUMMARY,
INFRA_TASK_NAME,
ORG_TOWER,
TASK_SCHLD_DURATION_IN_HRS,
TASK_CURRNT_DURATION_IN_HRS,
TASK_OVERDUE_HOURS,
SCHLD_END_EXCEEDED,
TASK_SCHLD_DURATION_IN_DAYS,
TASK_CURRNT_DURATION_IN_DAYS,
TASK_OVERDUE_DAYS,
SERVER_COUNT,
CHANGE_LOCATION,
OPS_CAT3,
LOCATION,
"SERVER_TYPE",
TOWER,
"OVERDUE BRACKET",
sum(SERVER_COUNT) OVER (
PARTITION BY PARENT_CHANGE_ID ORDER BY INFRA_CHANGE_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS SERVER_COUNT,
row_number() OVER (PARTITION BY PARENT_CHANGE_ID ORDER BY INFRA_CHANGE_ID) AS rn
FROM t1
)
SELECT * FROM t2 WHERE rn = 1;
```
Message was edited by: Rakesh119
The exact error message is:
ORA-00918: column ambiguously defined
00918. 00000 - "column ambiguously defined"
*Cause:
*Action:
Error at Line: 169 Column: 8