Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Select location where end_date is null (ie active)

Shambo2904Sep 12 2019 — edited Sep 12 2019

Morning All,

I have a query which seems to be straightforward but having issues trying to get the data needed. I am using oracle version 12c. The sql will be put into a package but firstly wanted to get it working in a sql session.. Details as below in a simplified form

create table test

(

LOCATION           VARCHAR2( 5 BYTE),

START_DATE      DATE,

END_DATE          DATE

);

INSERT INTO TEST

VALUES('TAB18','13-FEB-2019','15-APR-2019');

INSERT INTO TEST

VALUES('DHA18','15-APR-2019', '11-SEP-2019');

INSERT INTO TEST

VALUES('KHA18','11-SEP-2019',NULL);

COMMIT;

I wish to bring back the location whereby if  a particular date is within a date range then bring the location back for that. eg if date  was '01-mar-2019' then the location brought back would be 'TAB18' . However if the date is '11-SEP-2019' then I only need to bring back 'KHA18' as that is still active ie has no end date, rather than DHA18 and KHA18 because it falls within two date ranges.

select location from test

where (to_date('11-SEP-2019','DD-MON-RRRR')  BETWEEN START_DATE AND END_DATE

             OR END_DATE IS NULL);

I believe 'coalesce' could be used possibly in this instance but not sure how.

Thanks in advance

Regards

Comments
Post Details
Added on Sep 12 2019
14 comments
4,534 views