What does this mean? If I alias every column in the with clause I get the same error. I did notice that if I replace this view abw_v_exist_mbr_alt_hierarchy with a hard coded select the query works.
ORA-32039: recursive WITH clause must have column alias list
[code]
with bridge as (
SELECT
AVS.pontis_bridge_id,
AVS.super_struct_name structure_name,
ALV.name vehicle_name,
AVS.inv_rf inv_rating_factor,
AVS.opr_rf opr_rating_factor,
AVS.post_rf legal_rating_factor,
AVS.safe_rf permit_rating_factor,
sys_type_display.display inv_rating_method,
AVS.up_to_date_ind,
AE.event_descr,
AE.event_timestamp,
AP.username,
AVS.event_id,
SYS_TYPE_IMPACT.display sys_type_impact,
SYS_TYPE_LANE.display sys_type_lane
FROM
(SELECT
AVEMAH.pontis_bridge_id,
AVEMAH.super_struct_name,
ARRS.vehicle_id,
min(ARRS.inv_rf) inv_rf,
min(ARRS.opr_rf) opr_rf,
min(ARRS.post_rf) post_rf,
min(ARRS.safe_rf) safe_rf,
ARRS.design_method_type,
ASMAE.event_id,
ASMAE.up_to_date_ind,
ARRS.impact_loading_type,
ARRS.lane_loading_type
FROM abw_v_exist_mbr_alt_hierarchy AVEMAH, abw_spng_mbr_alt_events ASMAE, abw_rating_results_summary ARRS
WHERE
AVEMAH.bridge_id=ASMAE.bridge_id
and ASMAE.bridge_id=ARRS.bridge_id
and AVEMAH.struct_def_id=ASMAE.struct_def_id
and ASMAE.struct_def_id=ARRS.struct_def_id
and AVEMAH.super_struct_mbr_id=ASMAE.super_struct_mbr_id
and ASMAE.super_struct_mbr_id=ARRS.super_struct_mbr_id
and AVEMAH.super_struct_spng_mbr_alt_id=ASMAE.super_struct_spng_mbr_alt_id
and ASMAE.super_struct_spng_mbr_alt_id=ARRS.super_struct_spng_mbr_alt_id
and ASMAE.event_id = ARRS.event_id
and ASMAE.results_use_type=37601
GROUP BY AVEMAH.pontis_bridge_id,AVEMAH.super_struct_name, ARRS.vehicle_id,ARRS.design_method_type, ASMAE.event_id, ASMAE.up_to_date_ind,ARRS.impact_loading_type,ARRS.lane_loading_type
) AVS,
abw_lib_vehicle ALV,
abw_event AE,
abw_person AP,
abw_sys_type sys_type_display,
abw_sys_type sys_type_impact,
abw_sys_type sys_type_lane
WHERE AVS.event_id = AE.event_id
AND AE.entered_by= AP.person_id
AND AVS.vehicle_id=ALV.vehicle_id
AND AVS.impact_loading_type = SYS_TYPE_IMPACT.sys_type
AND AVS.lane_loading_type = SYS_TYPE_LANE.sys_type
AND AVS.design_method_type = sys_type_display.sys_type)
select
t.pontis_bridge_id BRIDGE_NAME,
t.event_timestamp LOAD_RATING_DATE,
MAX(CASE WHEN t.vehicle_name = 'HL-93 (US)' THEN round (t.inv_rating_factor, 2) ELSE NULL END) LOAD_RATING_HL93_INV_FACTOR,
MAX(CASE WHEN t.vehicle_name = 'HL-93 (US)' THEN round (t.opr_rating_factor, 2) ELSE NULL END) LOAD_RATING_HL93_OP_FACTOR,
MAX(CASE WHEN t.vehicle_name = 'H 20-44' THEN trunc (t.inv_rating_factor * 36) ELSE NULL END) H_INV_RATING_TONS,
MAX(CASE WHEN t.vehicle_name = 'H 20-44' THEN trunc (t.opr_rating_factor * 36) ELSE NULL END) H_OP_RATING_TONS,
MAX(CASE WHEN t.vehicle_name = 'HS 20-44' THEN trunc (t.inv_rating_factor * 36) ELSE NULL END) HS_INV_RATING_TONS,
MAX(CASE WHEN t.vehicle_name = 'HS 20-44' THEN trunc (t.opr_rating_factor * 36) ELSE NULL END) HS_OP_RATING_TONS,
MAX(CASE WHEN t.inv_rating_method='LRFR' THEN trunc (t.inv_rating_factor * 36) ELSE NULL END) LRFR_INV_RATING,
MAX(CASE WHEN t.opr_rating_method='LRFR' THEN trunc (t.opr_rating_factor * 36) ELSE NULL END) LRFR_OP_RATING,
MAX(CASE WHEN t.vehicle_name = 'SU4' THEN round (t.legal_rating_factor, 2) ELSE NULL END) LOAD_RATING_SU4_LEGAL_FACTOR,
MAX(CASE WHEN t.vehicle_name = 'Type 3S2' THEN round (t.legal_rating_factor, 2) ELSE NULL END) LOAD_RATING_3S2_LEGAL_FACTOR,
MAX(CASE WHEN t.vehicle_name = 'Type 7 Divisible Load Vehicle' THEN round (t.permit_rating_factor, 2) ELSE NULL END) LOAD_RATING_TYP7_PERM_FACTOR,
MAX(CASE WHEN t.vehicle_name = 'Type 6A Divisible Load Vehicle' THEN round (t.permit_rating_factor, 2) ELSE NULL END) LOAD_RATING_TYP6A_PERM_FACTOR,
MAX(CASE WHEN t.vehicle_name = 'HL-93 (US)' THEN round (t.permit_rating_factor, 2) ELSE NULL END) LOAD_RATING_HL93_PERM_FACTOR,
(CASE WHEN t.inv_rating_method='LFD' THEN 1
WHEN t.inv_rating_method='ASD' THEN 2
WHEN t.inv_rating_method='LRFD' THEN 3
WHEN t.inv_rating_method='LRFR' THEN 8 ELSE NULL END) LOADRATE_METHOD_ID,
t.username LOAD_RATING_SUBMITTED_BY
from bridge t,
(select q.pontis_bridge_id, max(q.event_timestamp) max_et
from bridge q
where q.impact_loading = 'As Requested'
and q.lane_loading = 'As Requested'
group by q.pontis_bridge_id) max_event_timestamp
where t.impact_loading = 'As Requested'
and t.lane_loading = 'As Requested'
and t.pontis_bridge_id = max_event_timestamp.pontis_bridge_id
and t.event_timestamp = max_event_timestamp.max_et
and t.username = 'PCAMPISI'
GROUP BY
t.pontis_bridge_id,
t.event_timestamp,
t.username,
t.inv_rating_method;
[/code]