Nad's questions

Eu escrevi um procedimento armazenado onde, com base no SAP_ID, atualizarei os dados na tabela, se os dados para isso SAP_IDjá existirem, caso contrário, eu INSERTos atualizarei.

Esta é a minha pergunta:

PROCEDURE INSERT_PF_EXCEL_DATA_IPCOLO (
        p_political_state_name         IN NVARCHAR2,
        p_political_state_code         IN NVARCHAR2,
        p_rfcdate                      IN NVARCHAR2,
        p_rfs_date                     IN NVARCHAR2,
        p_rfe1_date                    IN NVARCHAR2,
        p_site_drop_date               IN NVARCHAR2,
        p_ip_colo_siteid               IN NVARCHAR2,
        p_mw_installed                 IN NVARCHAR2,
        p_dg_nondg                     IN NVARCHAR2,
        p_eb_noneb                     IN NVARCHAR2,
        p_id_od_countchange            IN NVARCHAR2,
        p_id_od_changeddate            IN NVARCHAR2,
        p_rrh_countchange              IN NVARCHAR2,
        p_rrh_changeddate              IN NVARCHAR2,
        p_tenancy_countchange          IN NVARCHAR2,
        p_tenancy_changeddate          IN NVARCHAR2,
        p_sap_id                       IN NVARCHAR2,
        p_created_by                   IN NVARCHAR2,
        p_rfs_date_5g                  IN NVARCHAR2,
        p_drop_date_5g                 IN NVARCHAR2,
        p_olt_count                    IN NUMBER,
        p_olt_change_date              IN NVARCHAR2,
        p_diesel_downtime_minutes      IN NVARCHAR2,
        p_overall_infra_outage_minutes IN NVARCHAR2,
        p_diesel_downtime_min_my       IN NVARCHAR2,
        p_overall_infra_outage_min_my  IN NVARCHAR2,
        p_is5gpresent                  IN NVARCHAR2,
        p_is_site_dropped              IN NVARCHAR2,
        p_status                       OUT NVARCHAR2,
        p_message                      OUT NVARCHAR2
    ) AS
        t_cnt           NUMBER;
        vcnt_sapid      NUMBER;
        v_ipcolobilling VARCHAR2(25) := 'IpColoBilling';
    BEGIN

  --  DELETE FROM APP_LOG WHERE APP_NAME = 'IpColoBilling' AND TRUNC(LOG_TIME) = TRUNC(SYSDATE - 2);

        DELETE FROM app_log
        WHERE
                app_name = v_ipcolobilling
            AND ( log_time ) >= trunc(sysdate - 2)
            AND log_time < trunc(sysdate - 1);

        COMMIT;
        
        SELECT count(sap_id) INTO vcnt_sapid FROM temp_ipcolo_billing_mst
        WHERE
            sap_id = p_sap_id;-- AND CREATED_DATE = SYSDATE;


        IF vcnt_sapid > 0 THEN
            UPDATE temp_ipcolo_billing_mst
SET
    political_state_name = p_political_state_name,
    political_state_code = p_political_state_code,
    rfcdate = TO_DATE(p_rfcdate, 'DD-MM-YYYY'), -- Truncates to the day
    rfs_date = TO_DATE(p_rfs_date, 'DD-MM-YYYY'),-- TRUNC(), -- Removed invalid format 'dd-MM-yyyy'
    rfe1_date = TO_DATE(p_rfe1_date, 'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
   site_drop_date = TO_DATE(p_site_drop_date,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    ip_colo_siteid = p_ip_colo_siteid,
    mw_installed = p_mw_installed,
    dg_nondg = p_dg_nondg,
    eb_noneb = p_eb_noneb,
    id_od_countchange = p_id_od_countchange,
    id_od_changeddate = TO_DATE(p_id_od_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    rrh_countchange = p_rrh_countchange,
    rrh_changeddate = TO_DATE(p_rrh_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    tenancy_countchange = p_tenancy_countchange,
    tenancy_changeddate = TO_DATE(p_tenancy_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    created_by = p_created_by,
    rfs_date_5g = TO_DATE(p_rfs_date_5g,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    drop_date_5g = TO_DATE(p_drop_date_5g,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    olt_count = p_olt_count,
    olt_change_date = TO_DATE(p_olt_change_date,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    diesel_downtime_minutes = p_diesel_downtime_minutes,
    overall_infra_outage_minutes = p_overall_infra_outage_minutes,
    diesel_downtime_min_my = p_diesel_downtime_min_my,
    overall_infra_outage_min_my = p_overall_infra_outage_min_my,
    is5gpresent = p_is5gpresent,
    is_site_dropped = p_is_site_dropped,
    LAST_UPDATED_DATE = SYSDATE
    
WHERE

    sap_id = p_sap_id;
                
                p_message := 'SUCCESS';

            COMMIT;
        ELSE
            INSERT INTO temp_ipcolo_billing_mst (--TEMP_IPCOLO_BILLING_MST

                id,
                political_state_name,
                political_state_code,
                rfcdate,
                rfs_date,
                rfe1_date,
                site_drop_date,
                ip_colo_siteid,
                mw_installed,
                dg_nondg,
                eb_noneb,
                id_od_countchange,
                id_od_changeddate,
                rrh_countchange,
                rrh_changeddate,
                tenancy_countchange,
                tenancy_changeddate,
                sap_id,
                created_by,
                rfs_date_5g,
                drop_date_5g,
                olt_count,
                olt_change_date,
                diesel_downtime_minutes,
                overall_infra_outage_minutes,
                diesel_downtime_min_my,
                overall_infra_outage_min_my,
                is5gpresent,
                is_site_dropped
            ) VALUES (
                incr_id_ipcolo_temp.NEXTVAL,
                p_political_state_name,
                p_political_state_code,
                to_date(p_rfcdate, 'dd-MM-yyyy'),
                to_date(p_rfs_date, 'dd-MM-yyyy'),
                to_date(p_rfe1_date, 'dd-MM-yyyy'),
                to_date(p_site_drop_date, 'dd-MM-yyyy'),
                p_ip_colo_siteid,
                p_mw_installed,
                p_dg_nondg,
                p_eb_noneb,
                p_id_od_countchange,
                to_date(p_id_od_changeddate, 'dd-MM-yyyy'),
                p_rrh_countchange,
                to_date(p_rrh_changeddate, 'dd-MM-yyyy'),
                p_tenancy_countchange,
                to_date(p_tenancy_changeddate, 'dd-MM-yyyy'),
                p_sap_id,
                p_created_by,
                to_date(p_rfs_date_5g, 'dd-MM-yyyy'),
                to_date(p_drop_date_5g, 'dd-MM-yyyy'),
                p_olt_count,
                to_date(p_olt_change_date, 'dd-MM-yyyy'),
                p_diesel_downtime_minutes,
                p_overall_infra_outage_minutes,
                to_date(p_diesel_downtime_min_my, 'dd-MM-yyyy'), 
                to_date(p_overall_infra_outage_min_my, 'dd-MM-yyyy'),
                p_is5gpresent,
                p_is_site_dropped
            );

            p_message := 'SUCCESS';
        END IF;

    EXCEPTION
        WHEN OTHERS THEN
            p_status := sqlerrm;
            p_message := 'ERROR';
    END INSERT_PF_EXCEL_DATA_IPCOLO;

Onde estou errando?

Tenho uma tabela onde desejo obter registros que MODIFIED_DATEsejam iguais à contagem de2

Por exemplo: se para job_id= 1234 houver 2 registros com data MODIFIED_DATE= 04-10-2024então ele deve filtrar esse registro.

Name                   Null?    Type           
---------------------- -------- -------------- 
JOB_PROGRESS_ID        NOT NULL NUMBER         
JOB_ID                          NUMBER         
STATUS_ID                       NUMBER         
HOTO_OFFERED_DATE               DATE           
HOTO_ACCEPTENCE_DATE            DATE           
LIT_ACCEPTENCE_DATE             DATE           
APPROVED_BY                     NVARCHAR2(200) 
APPROVED_DATE                   DATE           
REJECTED_BY                     NVARCHAR2(200) 
REJECTED_DATE                   DATE           
APPROV_REJECT_REMARK            NVARCHAR2(255)     
ABD_COMPLETED_LENGTH            NUMBER         
NE_SPAN_LENGTH                  NUMBER(10,4)   
CREATED_BY                      NVARCHAR2(200) 
CREATED_DATE           NOT NULL DATE           
MODIFIED_BY                     NVARCHAR2(200) 
MODIFIED_DATE                   DATE 

Eu tenho 2 tabelas nomeadas como TBL_FIBER_INV_JOBSe TBL_FIBER_INV_JOB_PROGRESS. Abaixo está a descrição da tabela de ambas as tabelas

TBL_FIBER_INV_JOBS (Master Table) & TBL_FIBER_INV_JOB_PROGRESS (Child Table)

TBL_FIBER_INV_JOBS (Master Table)
Name                      Null?    Type           
------------------------- -------- -------------- 
JOB_ID                    NOT NULL NUMBER         
SPAN_ID                            NVARCHAR2(100) 
LINK_ID                            NVARCHAR2(100) 
CREATED_BY                         NVARCHAR2(200) 
CREATED_DATE              NOT NULL DATE           
MAINTENANCEZONECODE                NVARCHAR2(50)  
MAINTENANCEZONENAME                NVARCHAR2(100) 
MAINT_ZONE_NE_SPAN_LENGTH          NUMBER(10,4)   
SPAN_TYPE                          NVARCHAR2(20)  
JOB_FLAG                           NUMBER         
MISSING_ABD_LENGTH                 NUMBER(38,10)  
REOFFERFLAG                        VARCHAR2(10)   


TBL_FIBER_INV_JOB_PROGRESS (Child Table)
Name                   Null?    Type           
---------------------- -------- -------------- 
JOB_PROGRESS_ID        NOT NULL NUMBER         
JOB_ID                          NUMBER         
STATUS_ID                       NUMBER 
APPROVED_BY                     NVARCHAR2(200) 
APPROVED_DATE                   DATE           
REJECTED_BY                     NVARCHAR2(200) 
REJECTED_DATE                   DATE           
APPROV_REJECT_REMARK            NVARCHAR2(255) 
DELAY_REASON                    NVARCHAR2(255) 
ISABDMISSING                    NUMBER         
HOTO_OFFERED_LENGTH             NUMBER         
LIT_OFFERED_LENGTH              NUMBER         
HOTO_ACTUAL_LENGTH              NUMBER         
LIT_ACTUAL_LENGTH               NUMBER         
ABD_COMPLETED_LENGTH            NUMBER         
NE_SPAN_LENGTH                  NUMBER(10,4)   
CREATED_BY                      NVARCHAR2(200) 
CREATED_DATE           NOT NULL DATE           
MODIFIED_BY                     NVARCHAR2(200) 
MODIFIED_DATE                   DATE           
UMS_GROUP_ASS_BY_ID             NUMBER         
UMS_GROUP_ASS_BY_NAME           NVARCHAR2(200) 
UMS_GROUP_ASS_TO_ID             NUMBER         
UMS_GROUP_ASS_TO_NAME           NVARCHAR2(200) 
UMS_GROUP_ASS_TO_DATE           DATE           
JOB_PROGRESS_FLAG      NOT NULL NUMBER 

Portanto, o caso é que pode haver vários JOB_IDna tabela filho com unique JOB_PROGESS_ID. Agora o que eu quero é

Um procedimento onde eu sempre quero o máximo JOB_PROGESS_IDdisso JOB_IDcom uma condição da tabela filho que é

Condição da tabela filho:- UMS_GROUP_ASS_BY_NAME = 'CMM'e UMS_GROUP_ASS_TO_NAME IS NULL. Quero apenas 3 colunas da tabela mestre, que éJOB_ID, SPAN_ID, LINK_ID

Como não consigo buscar o máximo JOB_PROGRESS_IDpara o arquivo JOB_ID. Por favor, sugira

ATUALIZAR

Tentei com a consulta abaixo, mas não é exato o que desejo.

select max(job_progress_id),hoto_actual_length from tbl_fiber_inv_job_progress where job_id = 86753 and ums_group_ass_by_name='CMM' and ums_group_ass_to_name is null group by hoto_actual_length;

select max(job_id), span_id, job_flag,nvl(missing_abd_length,0) missing_abd_length, maint_zone_ne_span_length, maintenancezonecode from tbl_fiber_inv_jobs where job_id = 86753 and job_flag = 0 and span_type <> 'FTTX' group by span_id, job_flag,missing_abd_length, maint_zone_ne_span_length,maintenancezonecode; -- 187375

Eu criei um SP onde estou executando uma lógica para trazer a V_SPANID_COUNTcontagem, mas sempre mostra 0 contagem, mesmo que haja algum registro, digamos 10-20 registros

Abaixo está meu SP..

create or replace PROCEDURE PROC_NELENGTH_LESS_THAN_CMMAPPROVED AS 

V_ERRORS NVARCHAR2(3000);
V_SPANID_COUNT   NUMBER := 0;

BEGIN

        FOR CUR_NE_DATA IN
        (
            SELECT COUNT(SPAN_LINK_ID) INTO V_SPANID_COUNT from CMP_PANINDIA_VIEW_AGING_UPD 
            WHERE NE_LENGTH < ROUTE_APPROVED_BY_CMM
            AND CMM_APPROVED_DATE IS NOT NULL
            AND MISSING_ASBUILT = 0 and SPAN_TYPE <> 'FTTX'
            AND job_progress_flag = 1
        )
LOOP

          dbms_output.put_line('COUNT OF DATA: ' || V_SPANID_COUNT);
          
END LOOP;  

END PROC_NELENGTH_LESS_THAN_CMMAPPROVED;

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;  

Eu tenho um procedimento armazenado onde desejo verificar várias IFcondições. Então eu escrevi a consulta abaixo. Mas não está compilando e dando erro como

Erro (97,5): PLS-00103: Encontrou o símbolo "IF" ao esperar um dos seguintes: ; O símbolo “SE” foi ignorado.

PROCEDURE GET_SPAN_BY_MZ
(
  PUSERTYPE IN NVARCHAR2,
  POPERATIONTYPE IN NVARCHAR2,
  PSPANTYPE IN NVARCHAR2,
  PMAINTZONECODE IN VARCHAR2,  
  PSPANDATA OUT SYS_REFCURSOR
)

AS
--SQLSTM VARCHAR2 (2000);

BEGIN

IF PUSERTYPE = 'Construction_Engineer_OL' OR PUSERTYPE = 'NHQ-PMO' THEN
      BEGIN       
       IF POPERATIONTYPE = 'NEW' THEN
         BEGIN
         IF PSPANTYPE = 'INTERCITY' THEN
              BEGIN
OPEN PSPANDATA FOR
                 
SELECT  TO_CHAR(TRIM(RJ_SPAN_ID)) AS SPAN_ID,
        TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE 
FROM NE.MV_SPAN@NE
WHERE LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 
      'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')                                  
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i') 
  AND INVENTORY_STATUS_CODE = 'IPL'
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE

INTERSECT

select rj_span_id, rj_maintenance_zone_code 
from ne.mv_transmedia@ne
where inventory_Status_code = 'IPL'
  and LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 
      'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i')
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE;   
  
END IF;

END IF;

END GET_SPAN_BY_MZ;

Por favor, deixe-me saber onde estou errado.

Eu tenho um nome de coluna SAP_IDcujo valor vem como 'I-JK-SGAR-ENB-H021'. SO I want parte ENB desta coluna.

Abaixo está a consulta:

select  SAP_ID, SITE_TYPE, SITEBACKHAUL, PRIORITY_SITE, RJ_COMPANY_CODE_1,  
BUSINESSRANKING, USAGE_TYPE, '1' AS STATUS
FROM R4G_OSP.ENODEB
 where SAP_ID IS NOT NULL;

Eu tenho uma consulta onde estou executando e recebo o erro abaixo como

ORA-01722: número inválido

Aqui está a consulta.

SELECT TO_CHAR (tfc.linkid) spanid,
            TO_CHAR (tfc.mz_code) AS maint_zone_code,
            TO_CHAR (tfc.mz_name) AS maint_zone_name,
            SUM (tfc.mho_handover_cert) AS ne_length,
            TRUNC (tfc.created_date) AS offered_date     
       FROM app_lco.tbl_fip_checklist tfc
      WHERE     LENGTH (TRIM (tfc.linkid)) > 8
            AND LENGTH (TRIM (tfc.linkid)) < 21
            AND tfc.status = 'APPROVED'
   GROUP BY TO_CHAR (tfc.linkid),
            TO_CHAR (tfc.mz_code),
            TO_CHAR (tfc.mz_name),
            TRUNC(tfc.created_date);

A descrição da tabela está abaixo

Name                 Null Type           
-------------------- ---- --------------     
MHO_HANDOVER_CERT         VARCHAR2(20)   
STRAIGHT_LINE_DIAG        VARCHAR2(5)    
OFFERID                   VARCHAR2(10)   
CREATED_DATE              DATE           
SUBMITTED_BY              VARCHAR2(50)   
LINKID                    VARCHAR2(50)   
SPANID                    VARCHAR2(50)   
STATUS                    VARCHAR2(15)   
FILE_NAME                 VARCHAR2(1000) 
SAP_ID                    VARCHAR2(30)   
STATE_NAME                VARCHAR2(50)   
MZ_NAME                   VARCHAR2(70)   
MZ_CODE                   VARCHAR2(30)   

Eu tenho um método onde desejo executar um procedimento armazenado e obter o status e a resposta dele. Ao executar meu cmd.ExecuteNonQuery(), estou recebendo um erro:

a operação não é válida devido ao estado atual do objeto

Este é o código:

public static string GET_IPL_STATUS_ON_NEID(string NEID)
{
        string strStatus = string.Empty; string strMessage = string.Empty;

        OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionStringAppAuto"].ConnectionString);

        try
        {
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = ConfigurationManager.AppSettings["GET_IP_STATUS_PROC"].ToString();

            cmd.Parameters.Add(new OracleParameter { ParameterName = "P_A6_NEID", Value = NEID, OracleDbType = OracleDbType.Varchar2, Direction = ParameterDirection.Input });

            cmd.Parameters.Add(new OracleParameter
            {
                ParameterName = "STATUS",
                OracleDbType = OracleDbType.Varchar2,
                Size = 500,
                Direction = ParameterDirection.Output
            });

            cmd.Parameters.Add(new OracleParameter
            {
                ParameterName = "MESSAGE",
                OracleDbType = OracleDbType.Varchar2,
                Size = 500,
                Direction = ParameterDirection.Output
            });

            if (conn.State != ConnectionState.Open) conn.Open();
            
            cmd.ExecuteNonQuery();

            strStatus = cmd.Parameters["STATUS"].Value.ToString();
            strMessage = cmd.Parameters["MESSAGE"].Value.ToString();
        }
        catch (Exception ex)
        {   
            throw;
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
        }

        return strStatus;            
}

Atualmente, estou enviando um arquivo Excel para carregar e exibir dados em um formulário. Então estou validando com básico que dtnão deve ser nulo e a contagem deve ser maior que 0. Mas também quero uma validação de que se dta coluna for banco para ex: STATEcoluna primeira linha na minha imagem abaixo, ela deverá exibir um alerta de que o Excel tem uma coluna em branco. Como posso conseguir isso.

[![IMAGEM][1]][1]

Abaixo está o que eu tentei, mas ainda está exceto os valores em branco.

private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr = "";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
                         .ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
                          .ConnectionString;
                break;
        }

        conStr = String.Format(conStr, FilePath, isHDR);

        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        connExcel.Open();

        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();            

        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();

        if (dt != null && dt.Rows.Count > 0)
        {
            if (dt.Rows.Count <= Convert.ToInt32(ConfigurationManager.AppSettings["excelUploadSize_JioAF"]))  // check for 200 records
            {
                
            }
            else
            {
                ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Max upload size is 200 rows');", true);
            }
        }
        else 
        {
            ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Excel is blank..!!');", true);
        }

    }