CODEWITHSUNDEEP
oracle-databasedatestored-procedures
HEEN
HEEN

Reputation: 4727

Get data based on date specified condition in Oracle

I have a table whose description is below..

ID                       NOT NULL NUMBER         
CMP                               NVARCHAR2(100) 
SAP_ID                            NVARCHAR2(100) 
ID_OD_COUNTCHANGE                 NVARCHAR2(100) 
ID_OD_CHANGEDDATE                 DATE           
RRH_COUNTCHANGE                   NVARCHAR2(100) 
RRH_CHANGEDDATE                   DATE           
TENANCY_COUNTCHANGE               NVARCHAR2(100) 
TENANCY_CHANGEDDATE               DATE           
RFS_DATE                          DATE           
RFE1_DATE                         DATE           
INFRA_PROVIDER                    NVARCHAR2(100) 
IP_COLO_SITEID                    NVARCHAR2(100) 
SITE_NAME                         NVARCHAR2(500) 
R4GSTATE                          NVARCHAR2(100) 
MW_INSTALLED                      NVARCHAR2(100) 
DG_NONDG                          NVARCHAR2(100) 
EB_NONEB                          NVARCHAR2(100) 
TOWER_TYPE                        NVARCHAR2(100) 
VENDOR_CODE                       NVARCHAR2(100) 
RFCDATE                           DATE           
POLITICAL_STATE_NAME              NVARCHAR2(100) 
POLITICAL_STATE_CODE              NVARCHAR2(100) 
SITE_DROP_DATE                    DATE           
CITY_NAME                         NVARCHAR2(100) 
NEID                              NVARCHAR2(100) 
FACILITY_LATITUDE                 NVARCHAR2(100) 
FACILITY_LONGITUDE                NVARCHAR2(100) 
RJ_STRUCTURE_TYPE                 NVARCHAR2(100) 
RJ_JC_NAME                        NVARCHAR2(100) 
RJ_JC_CODE                        NVARCHAR2(100) 
COMPANY_CODE                      NVARCHAR2(100) 
BLCHAIN_RESP_MSG_MASTER           NVARCHAR2(100) 
BLCHAIN_RESP_CODE_MASTER          NVARCHAR2(100) 
SITE_ADDRESS                      CLOB

Now my condition in which I want to get the data is below.

RFS_DATE should be greater than or equal to RFE1_DATE TENANCY_CHANGEDDATE should be greater than or equal to RFS_DATE RRH_CHANGEDDATE should be greater than or equal to RFS_DATE ID_OD_CHANGEDDATE should be greater than or equal to RFS_DATE

Also below is my stored procedure in which I want the data to get fetched.

PROCEDURE GETENODEBDATA_BKC_INFO_INCREM
  (
    TBLDATA  OUT SYS_REFCURSOR
  )
 AS
BEGIN

      OPEN TBLDATA FOR   
      
        SELECT * FROM tbl_ipcolo_billing_mst where company_code is not null;
          

  END GETENODEBDATA_BKC_INFO_INCREM;

How to get the data by applying above conditions?

Upvotes: 0

Views: 34

Answers (2)

Popeye
Popeye

Reputation: 35920

Seems normal WHERE clause with condition ANDed

SELECT * FROM tbl_ipcolo_billing_mst where company_code is not null
--
AND RFS_DATE >= REF1_DATE 
AND TENANCY_CHANGEDDATE >= RFS_DATE 
AND RRH_CHANGEDDATE >= RFS_DATE 
AND ID_OD_CHANGEDDATE >= RFS_DATE

Cheers!!

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

Your logic would imply the following query:

SELECT *
FROM tbl_ipcolo_billing_mst
WHERE
    company_code IS NOT NULL AND
    RFS_DATE >= RFE1_DATE AND
    TENANCY_CHANGEDDATE >= RFS_DATE AND
    RRH_CHANGEDDATE >= RFS_DATE AND
    ID_OD_CHANGEDDATE >= RFS_DATE;

Upvotes: 1

Related Questions