I have used with statement to bring and fetch data in a procedure but while executing it I am getting error as
Error(550,37): PL/SQL: ORA-00904: "Q2_DATA"."AR_LENGTH": invalid identifier
Here is my query
WITH
q1_data (SPAN_ID, MAINT_ZONE_CODE, NE_LENGTH, UG_LENGTH, AR_LENGTH) AS
( SELECT rj_span_id AS SPAN_ID,
rj_maintenance_zone_code AS maint_zone_code,
Round(SUM(Nvl(calculated_length, 0) / 1000), 4) AS NE_LENGTH,
Round(SUM(
CASE
WHEN rj_construction_methodology NOT LIKE '%AERIAL%'
AND rj_construction_methodology NOT LIKE '%CLAMP%'
OR rj_construction_methodology IS NULL THEN Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS UG_LENGTH,
Round(SUM(
CASE
WHEN rj_construction_methodology LIKE '%AERIAL%'
OR rj_construction_methodology LIKE '%CLAMP%' THEN Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS AR_LENGTH
FROM ne.mv_span@ne
WHERE Trim(rj_span_id) = PSPAN_ID
AND inventory_status_code = 'IPL'
AND NOT Regexp_like (Nvl(rj_intracity_link_id, '-'), '_9', 'i')
AND rj_maintenance_zone_code = PMAINTZONECODE
GROUP BY rj_span_id,
rj_maintenance_zone_code
),
q2_data (SPAN_ID, MAINT_ZONE_CODE, NE_LENGTH, UG_LENGTH, AR_LENGTH) AS
(
SELECT span_id as SPAN_ID,
maintenancezonecode AS MAINT_ZONE_CODE,
maint_zone_ne_span_length AS NE_LENGTH,
fsa_ug AS UG_LENGTH,
fsa_aerial AS AR_LENGTH
FROM tbl_fiber_inv_jobs
WHERE span_id = PSPAN_ID
)
SELECT
q1.SPAN_ID, q1.MAINT_ZONE_CODE,
q1_data.NE_LENGTH - Nvl(q2_data.NE_LENGTH, 0) as NE_LENGTH,
q1_data.UG_LENGTH - Nvl(q2_data.UG_LENGTH, 0) as UG_LENGTH,
q1_data.AR_LENGTH - Nvl(q2_data.AR_LENGTH, 0) as AR_LENGTH
FROM q1_data q1
LEFT JOIN q2_data q2 ON(q2.SPAN_ID = q1.SPAN_ID)
ORDER BY q1.SPAN_ID, q1.MAINT_ZONE_CODE;update
Here is the whole stored procedure
PROCEDURE Get_spaninfo_by_span_mz_new (pspan_id IN NVARCHAR2,
pmaintzonecode IN VARCHAR2,
pspantype IN NVARCHAR2,
pspaninfodata OUT SYS_REFCURSOR)
AS
VAR_PARTIAL_QTY NUMBER;
BEGIN
VAR_PARTIAL_QTY :=0;
SELECT COUNT(JOB_ID) INTO VAR_PARTIAL_QTY from tbl_fiber_inv_jobs
where span_id = PSPAN_ID
AND JOB_FLAG = 1;
IF pspantype = 'INTERCITY' AND VAR_PARTIAL_QTY = 0
AND Length(pspan_id) = 21 THEN
BEGIN
OPEN pspaninfodata FOR
SELECT rj_span_id AS span_id,
rj_maintenance_zone_code AS
maint_zone_code,
Round(SUM(Nvl(calculated_length, 0) / 1000), 4) AS
ne_length,
Round(SUM(CASE
WHEN rj_construction_methodology NOT LIKE
'%AERIAL%'
AND rj_construction_methodology NOT LIKE
'%CLAMP%'
OR rj_construction_methodology IS NULL
THEN
Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS
ug_length,
Round(SUM(CASE
WHEN rj_construction_methodology LIKE
'%AERIAL%'
OR rj_construction_methodology LIKE
'%CLAMP%'
THEN
Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS
ar_length
FROM ne.mv_span@ne
-- FROM APP_FTTX.span@sat
WHERE Trim(rj_span_id) = pspan_id
AND inventory_status_code = 'IPL'
AND NOT Regexp_like (Nvl(rj_intracity_link_id, '-'), '_9',
'i')
AND rj_maintenance_zone_code = pmaintzonecode
GROUP BY rj_span_id,
rj_maintenance_zone_code;
END;
ELSE
WITH
q1_data (SPAN_ID, MAINT_ZONE_CODE, NE_LENGTH, UG_LENGTH, AR_LENGTH) AS
( SELECT rj_span_id AS SPAN_ID,
rj_maintenance_zone_code AS maint_zone_code,
Round(SUM(Nvl(calculated_length, 0) / 1000), 4) AS NE_LENGTH,
Round(SUM(
CASE
WHEN rj_construction_methodology NOT LIKE '%AERIAL%'
AND rj_construction_methodology NOT LIKE '%CLAMP%'
OR rj_construction_methodology IS NULL THEN Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS UG_LENGTH,
Round(SUM(
CASE
WHEN rj_construction_methodology LIKE '%AERIAL%'
OR rj_construction_methodology LIKE '%CLAMP%' THEN Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS AR_LENGTH
FROM ne.mv_span@ne
WHERE Trim(rj_span_id) = PSPAN_ID
AND inventory_status_code = 'IPL'
AND NOT Regexp_like (Nvl(rj_intracity_link_id, '-'), '_9', 'i')
AND rj_maintenance_zone_code = PMAINTZONECODE
GROUP BY rj_span_id,
rj_maintenance_zone_code
),
q2_data (SPAN_ID, MAINT_ZONE_CODE, NE_LENGTH, UG_LENGTH, AR_LENGTH) AS
(
SELECT span_id as SPAN_ID,
maintenancezonecode AS MAINT_ZONE_CODE,
maint_zone_ne_span_length AS NE_LENGTH,
fsa_ug AS UG_LENGTH,
fsa_aerial AS AR_LENGTH
FROM tbl_fiber_inv_jobs
WHERE span_id = PSPAN_ID
)
SELECT
q1.SPAN_ID, q1.MAINT_ZONE_CODE,
q1.NE_LENGTH - Nvl(q2.NE_LENGTH, 0) as NE_LENGTH,
q1.UG_LENGTH - Nvl(q2.UG_LENGTH, 0) as UG_LENGTH,
q1.AR_LENGTH - Nvl(q2.AR_LENGTH, 0) as AR_LENGTH
FROM q1_data q1
LEFT JOIN q2_data q2 ON(q2.SPAN_ID = q1.SPAN_ID)
ORDER BY q1.SPAN_ID, q1.MAINT_ZONE_CODE;
END IF;
END get_spaninfo_by_span_mz_new;Update 2
Here is my updated version of procedure
PROCEDURE Get_spaninfo_by_span_mz_new
(
pspan_id IN NVARCHAR2,
pmaintzonecode IN VARCHAR2,
pspantype IN NVARCHAR2,
pspaninfodata OUT SYS_REFCURSOR
)
AS
VAR_PARTIAL_QTY NUMBER;
BEGIN
VAR_PARTIAL_QTY :=0;
SELECT COUNT(JOB_ID) INTO VAR_PARTIAL_QTY from tbl_fiber_inv_jobs
where span_id = PSPAN_ID
AND JOB_FLAG = 1;
WITH
q1_data (SPAN_ID, MAINT_ZONE_CODE, NE_LENGTH, UG_LENGTH, AR_LENGTH) AS
( SELECT rj_span_id AS SPAN_ID,
rj_maintenance_zone_code AS maint_zone_code,
Round(SUM(Nvl(calculated_length, 0) / 1000), 4) AS NE_LENGTH,
Round(SUM(
CASE
WHEN rj_construction_methodology NOT LIKE '%AERIAL%'
AND rj_construction_methodology NOT LIKE '%CLAMP%'
OR rj_construction_methodology IS NULL THEN Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS UG_LENGTH,
Round(SUM(
CASE
WHEN rj_construction_methodology LIKE '%AERIAL%'
OR rj_construction_methodology LIKE '%CLAMP%' THEN Nvl(calculated_length, 0)
ELSE 0
END) / 1000, 4) AS AR_LENGTH
FROM ne.mv_span@ne
WHERE Trim(rj_span_id) = PSPAN_ID
AND inventory_status_code = 'IPL'
AND NOT Regexp_like (Nvl(rj_intracity_link_id, '-'), '_9', 'i')
AND rj_maintenance_zone_code = PMAINTZONECODE
GROUP BY rj_span_id,
rj_maintenance_zone_code
),
q2_data (SPAN_ID, MAINT_ZONE_CODE, NE_LENGTH, UG_LENGTH, AR_LENGTH) AS
(
SELECT span_id as SPAN_ID,
maintenancezonecode AS MAINT_ZONE_CODE,
maint_zone_ne_span_length AS NE_LENGTH,
fsa_ug AS UG_LENGTH,
fsa_aerial AS AR_LENGTH
FROM tbl_fiber_inv_jobs
WHERE span_id = PSPAN_ID
);
IF pspantype = 'INTERCITY' AND Length(pspan_id) = 21
AND VAR_PARTIAL_QTY = 0
THEN
BEGIN
OPEN pspaninfodata FOR
SELECT rj_span_id AS span_id, rj_maintenance_zone_code AS maint_zone_code,
Round(SUM(Nvl(calculated_length, 0) / 1000), 4) AS ne_length,
Round(SUM(CASE
WHEN rj_construction_methodology NOT LIKE
'%AERIAL%' AND rj_construction_methodology NOT LIKE
'%CLAMP%' OR rj_construction_methodology IS NULL
THEN
Nvl(calculated_length, 0) ELSE 0 END) / 1000, 4) AS UG_LENGTH,
Round(SUM(CASE WHEN rj_construction_methodology LIKE '%AERIAL%'
OR rj_construction_methodology LIKE '%CLAMP%'
THEN
Nvl(calculated_length, 0) ELSE 0 END) / 1000, 4) AS AR_LENGTH
FROM ne.mv_span@ne
WHERE Trim(rj_span_id) = pspan_id
AND inventory_status_code = 'IPL'
AND NOT Regexp_like (Nvl(rj_intracity_link_id, '-'), '_9',
'i')
AND rj_maintenance_zone_code = pmaintzonecode
GROUP BY rj_span_id,
rj_maintenance_zone_code;
END;
ELSE
OPEN pspaninfodata FOR
SELECT
q1_data.SPAN_ID, q1_data.MAINT_ZONE_CODE,
q1_data.NE_LENGTH - Nvl(q2_data.NE_LENGTH, 0) as NE_LENGTH,
q1_data.UG_LENGTH - Nvl(q2_data.UG_LENGTH, 0) as UG_LENGTH,
q1_data.AR_LENGTH - Nvl(q2_data.AR_LENGTH, 0) as AR_LENGTH
FROM q1_data q1
LEFT JOIN q2_data q2
ON(q2.SPAN_ID = q1.SPAN_ID)
ORDER BY q1.SPAN_ID, q1.MAINT_ZONE_CODE;
END IF;
END Get_spaninfo_by_span_mz_new; The problem is not with your WITH clauses, but your aliases at the bottom:
SELECT
q1.SPAN_ID, q1.MAINT_ZONE_CODE,
q1_data.NE_LENGTH - Nvl(q2_data.NE_LENGTH, 0) as NE_LENGTH,
q1_data.UG_LENGTH - Nvl(q2_data.UG_LENGTH, 0) as UG_LENGTH,
q1_data.AR_LENGTH - Nvl(q2_data.AR_LENGTH, 0) as AR_LENGTH
FROM q1_data q1
LEFT JOIN q2_data q2
You are aliasing q1_data as q1, and q2_data as q2. Once you put an alias on a row source (a table-like object in a from clause), any fully qualified references to columns in that table (as well as any table-referencing hints) must use the supplied alias, not the object name. So simply change it to:
SELECT
q1.SPAN_ID, q1.MAINT_ZONE_CODE,
q1.NE_LENGTH - Nvl(q2.NE_LENGTH, 0) as NE_LENGTH,
q1.UG_LENGTH - Nvl(q2.UG_LENGTH, 0) as UG_LENGTH,
q1.AR_LENGTH - Nvl(q2.AR_LENGTH, 0) as AR_LENGTH
FROM q1_data q1
LEFT JOIN q2_data q2