Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Oracle queries with keyword NOCYCLE not supported in CONNECT BY NOCYCLE PRIOR #3237

Closed
Hal-H2Apps opened this issue Mar 28, 2024 · 0 comments · Fixed by #3238
Closed

Oracle queries with keyword NOCYCLE not supported in CONNECT BY NOCYCLE PRIOR #3237

Hal-H2Apps opened this issue Mar 28, 2024 · 0 comments · Fixed by #3238
Assignees

Comments

@Hal-H2Apps
Copy link

Version
sqlglot 23.2.1.dev9

Notes
The query is working without the NOCYCLE keyword.

Fully reproducible code snippet
Oracle DB

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(100),
manager_id NUMBER
);

-- Insertion de données de test
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1, 'John Doe', NULL);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (2, 'Jane Smith', 1);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (3, 'Alice Johnson', 2);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (4, 'Bob Brown', 1);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (5, 'Eva Lee', 4);

Python:
import sqlglot

sqlglot.parse_one(sql="""SELECT employee_id, employee_name, manager_id
FROM test_employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;""", read='oracle')

Error message

Traceback (most recent call last):
File "/Applications/PyCharm.app/Contents/plugins/python/helpers/pydev/pydevconsole.py", line 364, in runcode
coro = func()
^^^^^^
File "", line 1, in
File "/usr/local/lib/python3.11/site-packages/sqlglot/init.py", line 124, in parse_one
result = dialect.parse(sql, **opts)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlglot/dialects/dialect.py", line 490, in parse
return self.parser(**opts).parse(self.tokenize(sql), sql)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlglot/parser.py", line 1163, in parse
return self._parse(
^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/sqlglot/parser.py", line 1232, in _parse
self.raise_error("Invalid expression / Unexpected token")
File "/usr/local/lib/python3.11/site-packages/sqlglot/parser.py", line 1273, in raise_error
raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 4, Col: 24.
_id, employee_name, manager_id
FROM test_employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

** Oracle Documentation **
https://docs.oracle.com/cd/B13789_01/server.101/b10759/queries003.htm

hierarchical_query_clause

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants