在Oracle语法错误中创建视图 [英] Create view in Oracle syntax error

问题描述

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;

我正在尝试在Oracle中创建一个视图。



编写查询（并另存为视图）以显示需要重新计算TOTAL_PAB的所有员工。如果他们的DATE_LAST_CALC在2015年4月1日到2015年4月4日之间（在那段时间内有病毒报告，因此他们想要重新计算这些）或2015年6月1日（财政年度开始）之间的差异，则需要重新计算它们）和DATE_LAST_CALC大于175天。按区的名称排序，然后按员工的姓氏排序



当我尝试运行时，我得到ORA-00933：SQL命令没有正确结束。我不知道语法有什么问题。请帮助。

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 1 st, 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.

推荐答案

最新版本的Oracle支持ORDER BY子句。



In你的查询我看到了几个问题。第一个是如果T1.DATE_LAST_CALC列的数据类型为DATE，则会出现错误。在Oracle中，您必须在比较之前转换为日期。其次，你在这里尝试做两件事。尝试在你减去的两个日期周围加上括号。

The ORDER BY clause is supported in recent versions of Oracle.

In your query I see a couple of problems. The first is if the T1.DATE_LAST_CALC column is of data type DATE, you'll get an error. In Oracle you have to convert to date before comparing. Secondly, you're trying to do 2 things here. Try putting brackets around the two dates your are subtracting.

OR '2015-06-01' - T1.DATE_LAST_CALC > 175

这应该很接近;如果没有，请告诉我：

This should be close; if not, let me know:

CREATE OR REPLACE VIEW your_view AS
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 TO_DATE('2015-04-01', 'yyyy-mm-dd') AND TO_DATE('2015-04-04', 'yyyy-mm-dd')
OR (TO_DATE('2015-06-01', 'yyyy-mm-dd') - T1.DATE_LAST_CALC) > 175
ORDER BY DISTRICT_NAME, EMP_LNAME;

这篇关于在Oracle语法错误中创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！