CODEWITHSUNDEEP
sqloracle-databasevieworacle-apex
Bob Marks
Bob Marks

Reputation: 139

SQLView not working

How come this is not working?

I am trying to create a view that views job vacancies that are between 2 dates.

1 being greater or equal to todays and the closing date being less than or equal to todays date.

Any ideas?

CREATE OR REPLACE FORCE EDITIONABLE VIEW  "VIEW_JOB_VACANCIES" ("JOB_ID", "JOB_TITLE", "JOB_DESCRIPTION", "JOB_EMAIL_ADDRESS", "JOB_CONTACT_NUMBER", "JOB_SALARY", "APPLICATION_METHOD", "START_DATE", "CLOSING_DATE", "SITE_ID") AS 
  SELECT job_id, job_title, job_description, job_email_address, job_contact_number, job_salary, application_method, start_date, closing_date, site_id 
FROM jobs
WHERE start_date >= sysdate
AND closing_date <= sysdate

Upvotes: 0

Views: 631

Answers (2)

djohnson10
djohnson10

Reputation: 165

I think the logic in your WHERE clause is backwards. The way you have it will only return records where the start date is after the closing date:

(start_date >= sysdate AND sysdate >= closing_date implies that start_date >= closing_date)

Try this:

WHERE start_date <= sysdate
AND closing_date >= sysdate

Does that help?

Upvotes: 2

Mike Risher
Mike Risher

Reputation: 21

Use between

CREATE OR REPLACE FORCE EDITIONABLE VIEW  "VIEW_JOB_VACANCIES" ("JOB_ID", "JOB_TITLE",     "JOB_DESCRIPTION", "JOB_EMAIL_ADDRESS", "JOB_CONTACT_NUMBER", "JOB_SALARY", "APPLICATION_METHOD", "START_DATE", "CLOSING_DATE", "SITE_ID") AS 
  SELECT job_id, job_title, job_description, job_email_address, job_contact_number,     job_salary, application_method, start_date, closing_date, site_id 
FROM jobs
WHERE sysdate between start_date AND closing_date

Upvotes: 2

Related Questions