Nad's questions

我已经编写了一个基于的存储过程SAP_ID，如果该数据已经存在，我将更新表中的数据，SAP_ID如果不存在，则我INSERT更新它。

这是我的疑问：

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;

我哪里做错了？

我有一张表，我想在其中获取MODIFIED_DATE与计数相同的记录2

例如：如果job_id= 1234 中有2 条记录的日期为MODIFIED_DATE=04-10-2024则应该过滤该记录。

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 

我有 2 个表，分别名为TBL_FIBER_INV_JOBS和TBL_FIBER_INV_JOB_PROGRESS。以下是这两个表的描述

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 

因此情况是JOB_ID子表中可以有多个具有唯一性的JOB_PROGESS_ID。现在我想要的是

JOB_PROGESS_ID我总是希望在子表的一个条件下获得最大值的过程JOB_ID是

子表的条件：-UMS_GROUP_ASS_BY_NAME = 'CMM'和UMS_GROUP_ASS_TO_NAME IS NULL。我只想要主表中的 3 列，即JOB_ID, SPAN_ID, LINK_ID

由于我无法获取JOB_PROGRESS_ID特定 的最大值JOB_ID。请提出建议

更新

我尝试了以下查询，但它并不准确。

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

我创建了一个 SP，在其中运行逻辑来计算V_SPANID_COUNT数量，但即使有记录（比如说 10-20 条记录），它始终显示 0 数量

下面是我的 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;

我已使用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;  

我有一个存储过程，我想在其中检查多个IF条件。所以我写了下面的查询。但它没有编译并给出错误

错误（97,5）：PLS-00103：在期望以下其中一项时遇到符号“IF”：；符号“IF”被忽略。

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;

请让我知道我错在哪里。

我有一个列名，SAP_ID其值来自'I-JK-SGAR-ENB-H021'. SO I want 该列的 ENB` 部分。

下面是查询：

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;

我有一个查询，我正在执行它并得到以下错误：

ORA-01722: 无效数字

这是查询。

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);

表说明如下

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)   

我有一个方法，我想执行存储过程并从中获取状态和响应。执行 my 时cmd.ExecuteNonQuery()，出现错误：

由于对象的当前状态，操作无效

这是代码：

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;            
}

目前，我正在上传一个Excel文件，以上传并以表单形式显示数据。因此，我正在使用 basic 进行验证，该值dt不应为 null 并且 count 应大于0。但我也想要一个验证，如果dt列是银行的前： STATE列第一行在我的下图中，那么它应该提示一个警报，Excel有一个空白列。我怎样才能实现它。

[![图片][1]][1]

下面是我尝试过的，但仍然排除空白值。

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);
        }

    }