Início / coding / Perguntas / 78563641
Accepted
hud
Asked: 2024-06-01 19:30:36 +0800 CST

Obtendo erro de identificador inválido de ORA - 00904 mesmo quando o nome da coluna é o mesmo

  • 772

Eu usei withuma instrução para trazer e buscar dados em um procedimento, mas ao executá-lo estou recebendo um erro como

Erro (550,37): PL/SQL: ORA-00904: "Q2_DATA"."AR_LENGTH": identificador inválido

Aqui está minha consulta

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;

atualizar

Aqui está todo o procedimento armazenado

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;

Atualização 2

Aqui está minha versão atualizada do procedimento

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 respostas

  1. Best Answer

    O problema não está nas suas WITHcláusulas, mas nos seus apelidos na parte inferior:

    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

    Você está usando o alias q1_datade as q1e q2_dataas q2. Depois de colocar um alias em uma origem de linha (um objeto semelhante a uma tabela em uma fromcláusula), quaisquer referências totalmente qualificadas a colunas nessa tabela (bem como quaisquer dicas de referência de tabela) deverão usar o alias fornecido , não o nome do objeto. Então simplesmente mude para:

    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

relate perguntas