Блог

Приведенная ниже процедура возвращает тысячи строк, передавая начальную и конечную даты. В дополнение к этому мне нужно добавить один параметр, который должен применять дату начала и дату окончания для разных столбцов, таких как дата создания, запланированная дата и дата окончания, которые доступны в наборе результатов.

Например, если я передаю значение как (дата создания,’31/10/2020 00:00:00′,’01/11/2020 00:00:00′), фильтр должен быть применен к созданному столбцу даты, если я прохожу (end,’31/10/2020 00:00:00′,’01/11/2020 00:00:00′), фильтр должен быть применен к столбцу даты окончания.

Как я могу этого добиться?

 create or replace PROCEDURE CVP_TEST(STARTDATE VARCHAR, ENDDATE VARCHAR) AS
  st_dt TIMESTAMP := TO_TIMESTAMP(STARTDATE, 'dd/mm/yyyy hh24:mi:ss');
  end_dt TIMESTAMP := TO_TIMESTAMP(ENDDATE, 'dd/mm/yyyy hh24:mi:ss');



  cursor cur1(start_time timestamp, end_time timestamp)
    is 




           SELECT
            A.LGST_GRP_CD ,

            (case when A.FRST_SHPG_LOC_CD like 'D%' then A.FRST_SHPG_LOC_CD
            when (A.FRST_SHPG_LOC_CD not like 'D%' and A.DMCL_CD is not null and length(dmcl_t.shpgloc_cd) <= 4) then dmcl_t.shpgloc_cd
            --when (A.FRST_SHPG_LOC_CD not like 'D%' and A.DMCL_CD is null and length(dom_carrier.shpgloc_cd) <= 4) then dom_carrier.shpgloc_cd 
            else A.FRST_SHPG_LOC_CD end) AS Dispatch_DC, 
            --ba_tmextra.FUN_GET_INT_DC(A.LD_LEG_ID) AS INT_DC,
            'XXX' AS INT_DC,

            A.CUR_OPTLSTAT_ID,

            --V.CUR_STAT_ID, 

            A.TRIP_ID AS TRIP_ID,
            A.LD_LEG_ID AS LOAD_ID,
            A.CARR_CD AS CARRIER_ID,      
            A.SRVC_CD   AS SERVICE_ID,
            A.EQMT_TYP          AS EQMT_TYP,
            A.STRD_DTT  AS START_DATE,    
            A.END_DTT AS END_DTT,
            A.CRTD_DTT AS CRTD_DTT,
            A.TRCTR_NUM as TRCTR_NUM,
            A.DRVR as DRVR,
            A.TRLR_NUM,
            A.FRST_SHPG_LOC_CD,
            A.LAST_SHPG_LOC_CD, 
            A.NUM_STOP  as NUM_STOP,

            --BA_TMEXTRA.FUN_GET_STOP_LOC(A.LD_LEG_ID, 0) STOP_LIST,
            'XXX' AS STOP_LIST,

            LD_MMO.PRTB_CTNT AS LD_COMMENTS,

            A.MILE_DIST          AS MILE_DIST,
            A.TOT_SCLD_WGT       AS TOTAL_WEIGHT,
            A.ELPD_HRS,
            CM.chrg_extl_code2 as chrg_extl_code2,
            (case when CM.chrg_extl_code2 =  'HAUL' then C.CHRG_CD else null end) as Haul_Charge_ID,
            (case when CM.chrg_extl_code2 =  'HAUL' then C.CHGD_UNIT_RATE  else null end) as Planned_Rate,
            (case when CM.chrg_extl_code2 =  'FUEL' then C.CHGD_UNIT_RATE  else null end) as Fuel_Planned_Rate,
            (case when CM.chrg_extl_code2 =  'FUEL' then C.CHRG_CD else null end) as Fuel_Surcharge_Type_ID,
            (case when CM.chrg_extl_code2 =  'STOP' then C.CHRG_CD else null end) as Stop_Off_Charge_ID,

            --C.CHRG_CD        AS CHRG_CD,
            --C.CHGD_UNIT_RATE AS PLANNED_RATE, 
            NVL(C.MNLY_OVRD_DLR,0) as CHRG_GRP_AMT
            FROM JDATM_PROD.LD_LEG_T A, JDATM_PROD.CHRG_DETL_T C, JDATM_PROD.IA_DIST_MSTRCHRG CM, 
            JDATM_PROD.DMCL_T, 
            --DMCL_T DOM_CARRIER, 
            --VCHR_AP_T V, 
            JDATM_PROD.MMO_T LD_MMO
            WHERE 1=1
            AND A.LD_LEG_ID = C.LD_LEG_ID
            AND C.CHRG_CD = CM.CHRG_CODE    
            AND C.CHRGDETL_TYP_ENU  = 1
            AND C.CHRG_LVL_ENU  != 7
            AND A.DMCL_CD=DMCL_T.DMCL_CD( )
            --AND DOM_CARRIER.CARR_CD( )=A.CARR_CD
            --AND V.VCHR_NUM ( ) = C.VCHR_NUM_AP
            --AND V.CUR_STAT_ID ( ) != 825
            and A.mmo_id = ld_mmo.mmo_id ( )
            AND A.TRIP_ID IS NULL
            --AND A.LD_LEG_ID = 1001038103
            AND A.RATG_VLID_YN = 'T'
            AND A.STRD_DTT BETWEEN   start_time and end_time;
           read_value cur1%ROWTYPE;

           BEGIN
           --dbms_output.put_line('Accepted:' || STARTDATE || ', ' || ENDDATE);
           --dbms_output.put_line('Assigned:' || st_dt || ', ' || end_dt);
  
  
           
          


END;


  

Условие ИЛИ можно использовать следующим образом:

 AND (
     (P_IN_COLUMN_NAME = 'created date' AND A.CREATED_DTT BETWEEN   start_time and end_time)
     OR
     (P_IN_COLUMN_NAME = 'planned date' AND A.PLANNED_DTT BETWEEN   start_time and end_time )
)