View in Oracle — syntax error

Question

SELECT D1.DISTRICT_NAME, E1.EMP_FNAME, E1.EMP_LNAME, T1.TAX_YEAR, T1.DATE_LAST_CALC
FROM DISTRICT AS D1, EMPLOYEE AS E1, TOTAL_PAB AS T1
WHERE T1.DATE_LAST_CALC BETWEEN '2015-04-01' AND '2015-04-04'
OR '2015-06-01' - T1.DATE_LAST_CALC > 175
ORDER BY DISTRICT_NAME, EMP_LNAME;

I'm trying to create a view in Oracle.

Write a query (and save as a view) to display all employees that need to have their TOTAL_PAB recalculated. They need to be recalculated if their DATE_LAST_CALC is between April 1st, 2015 and April 4th, 2015 (there was a virus report in that time frame so they want to recalculate these) OR the difference between June 1st, 2015 (beginning of fiscal year) and the DATE_LAST_CALC is greater than 175 days. Sort by the District’s name and then by the employee’s last name

When I try to run this, I get ORA-00933: SQL command not properly ended. I don't know what is wrong with the syntax. Any help please.

Are you sure that subtracting a date from another date in oracle yields a number of days? And why would it yield days and not, say, seconds? Or fortnights for that matter? — Mike Nakis
– Mike Nakis, Commented Nov 22, 2015 at 17:45
@MikeNakis: yes, this is how it is defined (and clearly documented) in Oracle — user330315
– user330315, Commented Nov 22, 2015 at 17:48
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Nov 22, 2015 at 19:09

score 5 · Accepted Answer · 2015-11-22 17:50:59Z

5

Oracle does not support the AS keyword for table alias, so

from district as d1, employee as e1, total_pab as t1

needs to be:

from district d1, employee e1, total_pab t1

But that query has more problems.

Because you are using the outdated, ancient implicit joins in the where clause you don't notice that you are missing the join conditions for the three tables.

If you rewrite this to use a proper JOIN operator this immediately gets visible:

SELECT d1.district_name, e1.emp_fname, e1.emp_lname, t1.tax_year, t1.date_last_calc
FROM district d1
  JOIN employee e1 ON ?????
  JOIN total_pab t1 ON ?????

Also the condition: or '2015-06-01' - t1.date_last_calc > 175 is a bug happen to wait. '2015-06-01' is not a date value, it's a string constant which is implicitly converted to date. It's better to use an explicit date value. Either using standard SQL literal:

date '2015-06-01' - t1.date_last_calc > 175`

or using Oracle's to_date() function:

to_date('2015-06-01', 'yyyy-mm-dd') - t1.date_last_calc > 175` is a bug

The same is true for your between condition.

^{There is also no reason to write everything in uppercase in SQL}

edited Nov 22, 2015 at 17:50

answered Nov 22, 2015 at 17:48

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

@apc: there is no distinct in the query

Collectives™ on Stack Overflow

View in Oracle — syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related