主页 / coding / 问题 / 78563641
Accepted
hud
Asked: 2024-06-01 19:30:36 +0800 CST

即使列名称相同，也会出现 ORA - 00904 的无效标识符错误

  • 772

我已使用with语句在过程中引入和获取数据，但在执行时出现错误：

错误（550,37）：PL/SQL：ORA-00904：“Q2_DATA”。“AR_LENGTH”：无效标识符

这是我的查询

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;

更新

这是整个存储过程

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;

更新2

这是我的更新版本的程序

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;

oracle

1 个回答

  1. Best Answer

    问题不在于您的WITH子句，而在于底部的别名：

    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

    您将别名设置q1_data为q1和q2_dataas q2。一旦您在行源（子句中的类似表的对象）上放置别名from，对该表中的列的任何完全限定引用（以及任何表引用提示）都必须使用提供的别名，而不是对象名称。所以只需将其更改为：

    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
    • 2

相关问题