I have been looking at this code for the past two days now and I can not seem to get it to work. It keeps giving me
ORA-00907: missing right parenthesis.
I know that this is a topic that comes up a lot but for some reason none of the examples I have seen has helped me. Can someone please tell me why I got this error and how do I fix it? I am pretty sure that it has nothing to do with my parenthesis, maybe it’s my CONSTRAINTS?
DROP TABLE T_customers CASCADE CONSTRAINTS;
DROP TABLE dvd_collection CASCADE CONSTRAINTS;
DROP TABLE vhs_collection CASCADE CONSTRAINTS;
CREATE TABLE T_customers (
customer_id VARCHAR2 (8) PRIMARY KEY,
last_name VARCHAR2 (30) NOT NULL,
first_name VARCHAR2 (20) NOT NULL,
street VARCHAR2 (30) NOT NULL,
city VARCHAR2 (30) NOT NULL,
state CHAR (2) NOT NULL,
CHECK (state IN ('GA','DC','VA','NY')),
zip_code CHAR (5)
CHECK (TO_NUMBER(zip_code)
BETWEEN 10000 AND 27999),
home_phone VARCHAR2 (12) UNIQUE,
work_phone VARCHAR2 (12) UNIQUE,
email VARCHAR2 (95) NOT NULL);
CREATE TABLE historys_T (
history_record VARCHAR2 (8),
customer_id VARCHAR2 (8),
CONSTRAINT historys_T_FK FOREIGN KEY (customer_id) REFERENCES T_customer
ON DELETE CASCADE,
order_id VARCHAR2 (10) NOT NULL,
CONSTRAINT fk_order_id_orders
REFERENCES orders
ON DELETE CASCADE);
CREATE TABLE orders (
order_id VARCHAR2 (10) PRIMARY KEY,
m_p_unique_id VARCHAR2 (10),
CONSTRAINT orders_FK FOREIGN KEY (m_p_unique_id) REFERENCES library (m_p_unique_id)
order_date DATE DEFAULT);
CREATE TABLE library_T (
m_p_unique_id VARCHAR2 (10) PRIMARY KEY,
movie_title VARCHAR2 (80) NOT NULL,
serial_number VARCHAR2 (10) NOT NULL,
movie_id_number VARCHAR2 (10) NOT NULL,
movie_cast VARCHAR2 (100) NOT NULL,
movie_format CHAR (3) NOT NULL,
CONSTRAINT library_FK REFERENCES formats (movie_format));
CREATE TABLE formats_T (
movie_format CHAR (3) PRIMARY KEY,
movie_title VARCHAR2 (80) NOT NULL,
m_p_unique_id VARCHAR2 (10) NOT NULL,
CONSTRAINT format_FK REFERENCES library (m_p_unique_id));
CREATE TABLE dvd_collection (
m_p_unique_id VARCHAR2 (10) NOT NULL,
serial_number VARCHAR2 (10) NOT NULL,
movie_id_number VARCHAR2 (10) NOT NULL,
movie_title VARCHAR2 (80) NOT NULL,
movie_cast VARCHAR2 (100) NOT NULL,
movie_format VARCHAR2 (80) NOT NULL,
movie_rating VARCHAR2 (6) NOT NULL,
movie_distributer VARCHAR2 (30) NOT NULL,
movie_price NUMBER (3,2) NOT NULL,
movie_length NUMBER (3) NOT NULL,
movie_award VARCHAR2 (175) NOT NULL,
movie_release DATE);
CREATE TABLE vhs_collection
(
m_p_unique_id VARCHAR2 (10)NOT NULL,
serial_number VARCHAR2 (10) NOT NULL,
movie_id_number VARCHAR2 (10) NOT NULL,
movie_title VARCHAR2 (80) NOT NULL,
movie_cast VARCHAR2 (100) NOT NULL,
movie_format VARCHAR2 (80) NOT NULL,
movie_rating VARCHAR2 (6) NOT NULL,
movie_distributer VARCHAR2 (30) NOT NULL,
movie_price NUMBER (3,2) NOT NULL,
movie_length NUMBER (3) NOT NULL,
movie_award VARCHAR2 (175) NOT NULL,
movie_release DATE);
Here are the results I get when I run the code:
Table dropped.
Table dropped.
Table dropped.
Table created.
ON DELETE CASCADE)
*
ERROR at line 10:
ORA-00907: missing right parenthesis
order_date DATE DEFAULT)
*
ERROR at line 6:
ORA-00907: missing right parenthesis
CONSTRAINT library_FK REFERENCES formats (movie_format))
*
ERROR at line 9:
ORA-00907: missing right parenthesis
CONSTRAINT format_FK REFERENCES library (m_p_unique_id))
*
ERROR at line 6:
ORA-00907: missing right parenthesis
Table created.
Table created.
ORA-00907: missing right parenthesis error occurs when a left parenthesis is used without a right parenthesis to close it in SQL statements such as create table, insert, select, subquery, and IN clause. The right parenthesis is missing. All parentheses must be used in pairs. SQL statements that include multiple items should be contained in parentheses. The error ORA-00907: missing right parenthesis will be thrown If the left parenthesis has been used in the SQL Statement but the right parenthesis is missing.
Oracle’s collection of items is denoted by a parenthesis. If the right parenthesis is missing, Oracle will be unable to recognise the items specified after that. The error message ORA-00907: missing right parenthesis will be shown. The right parenthesis indicates the closing of the item list. Oracle could not recognise the end of the items list if the right parenthesis was missing. All left parenthesis in Oracle SQL must be paired with a right parenthesis. You’ll receive this error ORA-00907: missing right parenthesis if there are more left parenthesis than right parentheses.
When the ORA-00906 error occurs
The collection of items could not be provided if the right parenthesis was missing in the SQL Statement such as create table, insert table, select subquery, and IN clause. Create a SQL query that should include a collection of items but does not include the right parenthesis. In this case, the error message will be displayed. The error will be resolved if the right parenthesis is added before the collection of items
Problem
create table dept(
id number primary key,
name varchar2(100)Error
Error starting at line : 3 in command -
create table dept(
id number primary key,
name varchar2(100)
Error report -
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"Root Cause
In Oracle, the collection of items is defined using enclosed parentheses. Oracle could not identify the closing of the collection of items list if the right parenthesis was missing. Oracle anticipates the right parenthesis after the list. Oracle will give an error if the right parenthesis is missing.
Solution 1
If the parenthesis in the anticipated SQL Statement is missing, the error will be thrown. The right parenthesis for specifying the item collection is missing. The error will be fixed if you add the missing right parentheses.
Problem
create table dept(
id number primary key,
name varchar2(100)
Error report -
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"Solution
create table dept(
id number primary key,
name varchar2(100)
);Solution 2
The column data type, as well as the size or precision of the data type, should be provided. The error will be thrown if the size of the data type is provided in the column definition without right parenthesis. Oracle will look for the size by enclosing a value in parentheses. The error message will be displayed if the right parenthesis is missing right after the data type size.
Problem
create table dept(
id number primary key,
name varchar2(100,
sal number
);
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"Solution
create table dept(
id number primary key,
name varchar2(100),
sal number
);Solution 3
The subqueries are added with a enclosed parenthesis in the where clause. If the right parenthesis is missing in the subquery, the error message will be shown.
Problem
select * from employee where deptid in (select id from dept ;
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"Solution
select * from employee where deptid in (select id from dept) ;Solution 4
The values in the IN clause. is enclosed with parenthesis. If the right parenthesis is missing, the closing of the list could not be identified. The error message will be shown.
Problem
select * from employee where deptid in (1,2 ;
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"Solution
select * from employee where deptid in (1,2) ;Solution 5
All left parenthesis in Oracle SQL must be paired with a right parenthesis. You’ll see this error ORA-00907: missing right parenthesis if there are more left parenthesis than right parentheses.
Problem
select * from employee where deptid in (select id from dept where name in (select name from branches) ;
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"Solution
select * from employee where deptid in (select id from dept where name in (select name from branches)) ;Did you get an ORA-00907: missing right parenthesis error? Learn what caused it and how to resolve it in this article.
ORA-00907 Cause
When working with Oracle SQL, all left parenthesis (the “(” character) must be paired with a right parenthesis character (the “)” character).
If there are more left parentheses than right parentheses, then you’ll get this error.
It can also be caused by syntax errors in your CREATE TABLE statement.
There are a few ways to resolve this error.
Solution 1 – Check Your Pairs of Parentheses
The first solution is to check that you have the correct number of parentheses.
If you’re using an IDE such as SQL Developer, you can put your cursor next to each parenthesis to see where the matching parenthesis is. If it’s in the right spot, great. If the match is showing up somewhere unexpected, then you’re missing a parenthesis.
This can often happen if you’re using nested functions.
Solution 2 – Check your CREATE TABLE Statement
If you get an ORA-00907 error when running a CREATE TABLE statement, it could be because of an incorrect reference to a foreign key.
For example:
CREATE TABLE order_test (
order_id NUMBER NOT NULL PRIMARY KEY,
order_date DATE NOT NULL,
customer_id NUMBER FOREIGN KEY REFERENCES customer(customer_id)
);Result:
Error starting at line : 3 in command - CREATE TABLE order_test ( order_id NUMBER NOT NULL PRIMARY KEY, order_date DATE NOT NULL, customer_id NUMBER FOREIGN KEY REFERENCES customer(customer_id) ) Error report - SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action:
This happens because we don’t need to have the words FOREIGN KEY when defining a foreign key inline (like we have here).
We can either:
- Remove the words FOREIGN KEY
- Declare the foreign key out of line (recommended)
Option A:
If you want to keep using the inline declaration, remove the words FOREIGN KEY:
CREATE TABLE order_test (
order_id NUMBER NOT NULL PRIMARY KEY,
order_date DATE NOT NULL,
customer_id NUMBER REFERENCES customer(customer_id)
);The issue with this approach is you don’t know the name of the foreign key, which can make maintenance harder.
It’s better to declare a foreign key on a different line and give it a specific name.
Option B:
Declare the foreign key with a name
CREATE TABLE order_test_prefer (
order_id NUMBER NOT NULL PRIMARY KEY,
order_date DATE NOT NULL,
customer_id NUMBER NOT NULL,
CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
REFERENCES customer (customer_id)
);This way, you can have the fk_order_customer as the constraint name, and can easily see and refer to it.
For a full guide on using the CREATE TABLE statement, including the syntax for Oracle, read my guide here.
Make sure your CREATE TABLE statement aligns with this syntax, and you shouldn’t have any issues.
So, that’s how you resolve the ORA-00907: missing right parenthesis error.
September 12, 2020
Hi,
I got ” ORA-00907: missing right parenthesis ” error in Oracle SQL ( Table create, DML and DDL operations ) .
Details of error are as follows.
SQL> create table test(id number; create table test(id number * ERROR at line 1: ORA-00907: missing right parenthesis ORA-00907 missing right parenthesis Cause: A left parenthesis has been entered without a closing right parenthesis, or extra information was contained in the parentheses. All parentheses must be entered in pairs. Action: Correct the syntax and retry the statement.
When you write queries in Oracle SQL, left parenthesis (the “(” character) must be paired with a right parenthesis character (the “)” character).
If you use more left parentheses than right parentheses, then you will get this error.
ORA-00907 Errors are seen in CREATE TABLE, CREATE CLUSTER, and INSERT commands or within subqueries such as WHERE clauses, UPDATE table SET column = (SELECT…) statements which all require an itemized list enclosed in parentheses.
To solve this error, add the right parenthesis, it will be solved as follows.
SQL> create table test(id number; create table test(id number * ERROR at line 1: ORA-00907: missing right parenthesis SQL> create table test(id number); Table created. SQL>
Do you want to learn Oracle SQL, then read the following articles.
Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course
3,033 views last month, 1 views today
Error ORA-00907 is a syntax error and therefore, is a commonly seen error by users writing code manually. This error indicates that there is a left parenthesis but no corresponding right parenthesis, or that additional information was contained within the parentheses.
To correct this error, you must find the part of code that contains the missing right parenthesis, insert the missing symbol in the correct spot, and run the statement again.
Error ORA-00907 can commonly occur in commands such as CREATE TABLE, CREATE CLUSTER, and INSERT, which all require an itemized list enclosed in parentheses. It can also occur within subqueries such as WHERE clauses, UPDATE table SET column = (SELECT…) statements.
In the following example, the missing right parenthesis after “20” would throw error ORA-00907:
CREATE TABLE employee
(
employee_name VARCHAR(20 NOT NULL,
employee_phone VARCHAR(10) NOT NULL,
PRIMARY KEY(employee_name)
)
This error also occurs often in cases in which quotation marks are improperly used. If you are using single quotation marks in phrases that are enclosed by other single quotation marks, you must add another single quotation by the inner single quotation. In other words, you cannot use 4 single quotation marks together (‘___’___’___’). It must be written as ‘___”___”___’.
For example, the following lines of code is missing the necessary number of quotation marks:
BEGIN
in_fieldname_list := ‘PROTOTYPE’ ’, ‘ ’ADDR1’ ’, ‘ ’CITY’ ’, ‘ ’STATE’ ’,’ ’ZIP’ ’, ‘ ’OAPPRAISAL’;
It should be:
BEGIN
in_fieldname_list := ‘ ‘ ‘PROTOTYPE’ ’, ‘ ’ADDR1’ ’, ‘ ’CITY’ ’, ‘ ’STATE’ ’,’ ’ZIP’ ’, ‘ ’OAPPRAISAL’ ‘ ’;
Error ORA-00907 specifically points to a missing right parenthesis while error ORA-00906 indicates that there is a missing left parenthesis.
To avoid seeing error ORA-00907, make sure to practice writing correct syntax. One of the easiest ways to ensure your syntax is correct is to use an integrated development environment that includes a source code editor. A good source code editor should offer features such as syntax highlighting, autocomplete, indentation, and matching brackets to help the coder automate the process of coding and make it easier to catch mistakes. With such features, you should be able to easily spot any line of code, which is causing error ORA-00907. If still facing this error after reviewing the syntax, check that it is not being caused by FULL/LEFT OUTER joins associated with Bug 4433936. While the bug has been fixed for later versions of Oracle, it can still be seen in earlier versions.
From your query I concluded minimal data structure:
HS_HR_OB_ACTION (action_id number)
HS_HR_EMPLOYEE (EMP_NUMBER number, emp_display_name varchar2(20))
HS_HR_OB_ACT_ALT_RECIPIENT (ACTALT_EMP_NUMBER number, action_id number)
For such data following query works in Oracle 10g. For Oracle 11 uncomment second line and comment third line. Please compare results with SQL Server.
SELECT p1.ACTION_ID,
-- (select LISTAGG(emp_display_name, ', ') WITHIN GROUP (ORDER BY emp_display_name)
(SELECT wmsys.wm_concat(EMP_DISPLAY_NAME)
FROM (
SELECT DISTINCT EMP.EMP_DISPLAY_NAME,ACTS.ACTION_ID
FROM HS_HR_OB_ACT_ALT_RECIPIENT ALT
LEFT JOIN HS_HR_EMPLOYEE EMP ON EMP.EMP_NUMBER=ALT.ACTALT_EMP_NUMBER
LEFT JOIN HS_HR_OB_ACTION ACTS ON ACTS.ACTION_ID=ALT.ACTION_ID
) p2
WHERE p2.ACTION_ID = p1.ACTION_ID) AS NAME
FROM (
SELECT DISTINCT EMP.EMP_DISPLAY_NAME,ACTS.ACTION_ID
FROM HS_HR_OB_ACT_ALT_RECIPIENT ALT
LEFT JOIN HS_HR_EMPLOYEE EMP ON EMP.EMP_NUMBER=ALT.ACTALT_EMP_NUMBER
LEFT JOIN HS_HR_OB_ACTION ACTS ON ACTS.ACTION_ID=ALT.ACTION_ID
) p1
GROUP BY ACTION_ID
I think this query may be yet simplified, but I didn’t want to interfere too much in original version at first.
SELECT distinct ACTION_ID, wmsys.wm_concat(EMP_DISPLAY_NAME) -- or listagg(...)
FROM HS_HR_OB_ACT_ALT_RECIPIENT ALT
LEFT JOIN HS_HR_EMPLOYEE EMP ON EMP.EMP_NUMBER=ALT.ACTALT_EMP_NUMBER
LEFT JOIN HS_HR_OB_ACTION ACTS ON ACTS.ACTION_ID=ALT.ACTION_ID
GROUP BY ACTION_ID
From your query I concluded minimal data structure:
HS_HR_OB_ACTION (action_id number)
HS_HR_EMPLOYEE (EMP_NUMBER number, emp_display_name varchar2(20))
HS_HR_OB_ACT_ALT_RECIPIENT (ACTALT_EMP_NUMBER number, action_id number)
For such data following query works in Oracle 10g. For Oracle 11 uncomment second line and comment third line. Please compare results with SQL Server.
SELECT p1.ACTION_ID,
-- (select LISTAGG(emp_display_name, ', ') WITHIN GROUP (ORDER BY emp_display_name)
(SELECT wmsys.wm_concat(EMP_DISPLAY_NAME)
FROM (
SELECT DISTINCT EMP.EMP_DISPLAY_NAME,ACTS.ACTION_ID
FROM HS_HR_OB_ACT_ALT_RECIPIENT ALT
LEFT JOIN HS_HR_EMPLOYEE EMP ON EMP.EMP_NUMBER=ALT.ACTALT_EMP_NUMBER
LEFT JOIN HS_HR_OB_ACTION ACTS ON ACTS.ACTION_ID=ALT.ACTION_ID
) p2
WHERE p2.ACTION_ID = p1.ACTION_ID) AS NAME
FROM (
SELECT DISTINCT EMP.EMP_DISPLAY_NAME,ACTS.ACTION_ID
FROM HS_HR_OB_ACT_ALT_RECIPIENT ALT
LEFT JOIN HS_HR_EMPLOYEE EMP ON EMP.EMP_NUMBER=ALT.ACTALT_EMP_NUMBER
LEFT JOIN HS_HR_OB_ACTION ACTS ON ACTS.ACTION_ID=ALT.ACTION_ID
) p1
GROUP BY ACTION_ID
I think this query may be yet simplified, but I didn’t want to interfere too much in original version at first.
SELECT distinct ACTION_ID, wmsys.wm_concat(EMP_DISPLAY_NAME) -- or listagg(...)
FROM HS_HR_OB_ACT_ALT_RECIPIENT ALT
LEFT JOIN HS_HR_EMPLOYEE EMP ON EMP.EMP_NUMBER=ALT.ACTALT_EMP_NUMBER
LEFT JOIN HS_HR_OB_ACTION ACTS ON ACTS.ACTION_ID=ALT.ACTION_ID
GROUP BY ACTION_ID
Содержание
- ORA-00907: missing right parenthesis
- Best Answer
- ORA-00907: missing right parenthesis
- Best Answer
- Answers
- Ora 00907 missing right parenthesis ошибка
- Базы данных
- Причина:
- Действие:
- 27.08.2009
- ora-904 ora-907 при использовании коррелированных подзапросов
- ora-904 «%s: invalid identifier»
- ora-907 «missing right parenthesis»
- ora-907
- 1 ответ 1
ORA-00907: missing right parenthesis
I am trying to run the query below with the input values:
:P6_QUICK_SELECT: This Year
It tells me that i am missing a right parenthesis. I dont see where i am.
Edited by: Dclipse03 on Jan 30, 2009 9:02 AM
Best Answer
Quick pointer — you may want to click on the «closing-quotes» icon on the rightmost side of your message box while replying. That makes it easy to differentiate the poster’s message from yours.
So what’s the final word on this ?
Does it work for you if you set the field as «display as text» and then use the PL/SQL block ?
Or does it become a «select list» if you set the field as «display as text», and hence unsuitable for a PL/SQL block ?
Here’s a very general idea.
— Can you set the value of a text field as the return value of an Oracle function ? In other words, can your application call a function in order to set the value of a text field ?
— Can you pass the value selected from a «select list» to an Oracle function ?
If your answers for both these questions are «yes», then you could do something like the following.
(1) Create two stored functions — «fn_from_date» and «fn_to_date» in the database. Each function takes in a string (varchar2) as the input value.
(2) Set the value of the «text field» :P6_FROM_DATE as the return value of the function «fn_from_date».
(3) Set the value of the «text field» :P6_TO_DATE as the return value of the function «fn_to_date».
(4) Pass the value of «select list» :P6_QUICK_SELECT to each function.
Make your application populate :P6_FROM_DATE and :P6_TO_DATE (by calling the functions «fn_from_date» and «fn_to_date») as soon as the end-user selects a value from the «select list» :P6_QUICK_SELECT». There must be some way of setting this — maybe as a property of the text field or something else.
The functions could use the logic mentioned in the earlier post:
Hope that helps,
isotope
Источник
ORA-00907: missing right parenthesis
on 11.2.0.4 on Win 2008
select creele,TRUNC((creele,’DD’)) from emprunts;
ORA-00907: missing right parenthesis (on column 28).
Let’s see the format of creele :
select creele from emprunts;
Best Answer
Because you’re telling it to do different things. Let’s suppose that there’s just one row in your table, with a rendule of 30/4/2015 and a creele of 20/4/2015.
A = 30/4/2015 — (20/4/2015 + 1) = 30/4/2015 — 21/4/2015 = 9
B = 30/4/2015 — 20/4/2015 + 1 = 10 + 1 = 11
If you want arithmetic operations to be carried out in a particular order, it’s best to use parentheses to make the order unambiguous. This would return the same value as A (if that’s what you want):
Answers
select creele,TRUNC(creele,’DD’) from emprunts;
do you want show only date- use to_char
select sysdate, trunc(sysdate,’DD’) , to_char(sysdate,’DD’) from dual
You try to add collection like values and syntax error is evident because single value or variable or expression is expected inside parenthesis. It is because parenthesis must return single result to upper nesting level (i might use wrong terms here because i did not find docs about this, yet)
select trunc ( ( ( sysdate — 1 ) ) ) from dual; is legal because sysdate -1 is expression that returns single result.
Date format is irrelevant and it depends session NLS_DATE_FORMAT setting how you see it.
So to fix your problem you must remove one nesting level that is extra parenthesis around creele,’DD’
Correct syntax is select creele, TRUNC( creele, ‘DD’ ) from emprunts;
and with dates ‘DD’ is default for function TRUNC.
so this returns same result select creele, TRUNC( creele ) from emprunts;
Источник
Ora 00907 missing right parenthesis ошибка
Базы данных
ORA-00907: упущены правые скобки
Причина:
Были введены левые скобки без закрытия правыми скобками или в скобках была заключена дополнительная информация. Все скобки должны вводится парами.
Действие:
Убедитесь, что вы имеете парное множество скобок, затем выполните выражение снова.
27.08.2009
ora-904 ora-907 при использовании коррелированных подзапросов
При использовании коррелированных подзапросов, т.е. подзапросов, результаты которых используются для каждой строки основного запроса — «a correlated subquery is evaluated once for each row», могут встречаются ошибки, неточно объясняющие, что делается неправильно
ora-904 «%s: invalid identifier»
ora-907 «missing right parenthesis»
Первая ошибка ora-904 в случае correlated subquery при отсутствии синтаксических ошибок сообщает об использовании более, чем одного уровня глубины вложенности (N-th level sub-query):
что не поддерживается Oracle (до версии 11.1.0.7 включительно) и стандартом SQL: ANSI SQL has table references (correlation names) scoped to just one level deep. Это пишет вице-президент Oracle T.Kyte, отвечая на соответствующий вопрос «Is there some sort of nesting limit for correlated subqueries?», т.е. информация официальная, из первых рук, пример оттуда же. В документации Oracle, начиная с 10.1, пишется иначе:
Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery
— но, это, видимо, долгосрочные планы Oracle.
ora-907
тут проблема старая и известная: запрет на использование ORDER BY в подзапросах, описанная в документации Oracle 7 (в документации следующих версий уже не упоминается — видимо, тоже есть планы по исправлению):
The ORDER BY clause cannot appear in subqueries within other statements.
По вышеупомянутой ссылке на сайте asktom.oracle.com можно найти пример замены конструкции с ORDER BY типа:
на аналитическую функцию типа :
для одновременного понижения уровня вложенности подзапроса (correlated query level deep) до 1 и устранения проблемы с ORDER BY в подзапросе — конструкция dense_rank first ORDER BY допускается Oracle.
Необходимо выбрать список значений Item_ID с самой поздней (максимальной) датой, но при выполнении выдаётся ошибка ORA-00907: missing right parenthesis. Если переводить дословно, то она указывает на несоответствие количества скобок. Подскажите, что может быть не так?
Сам фрагмент выбора максимальной даты работает:
1 ответ 1
В Oracle подзапросах не может быть фразы order by , т.к. она там не имеет никакого смысла. В итоге Oracle ожидает увидеть правую скобку сразу после фразы group by , однако натыкается на слово order и на нем сообщает, что не нашел скобку.
Кроме этого после исправления у вас возникнет вторая ошибка: подзапрос вернет более одной строки, что недопустимо в случае использования оператора = , следует использовать IN .
Правда этот запрос вероятно сделает не то, что вы хотели. Вы задаете group by Item_ID и берете таким образом для каждого Item_ID максимальную дату. Но для любого Item_ID найдется максимальная дата, в итоге вы получите полный список Item_ID из вашей таблицы. Поэтому, если вам нужны Item_ID с одинаковой, но самой большой в таблице датой — то следует убрать group by , либо заменить его на какой то, более отвечающий вашей задаче.
Источник