ORA-01788: CONNECT BY clause required in this query block

ORA-01788: CONNECT BY clause required in this query block error occurs when a query is created without a CONNECT BY clause and attempts to access the sql query’s pseudo column LEVEL. If the CONNECT BY clause is used in the oracle query, the pseudo column LEVEL is automatically created in the query output. If you try to access the pseudo column LEVEL in a query that does not include a CONNECT BY clause, the error ORA-01788: CONNECT BY clause required in this query block will be displayed.

The CONNECT BY clause is used to execute the query in a hierarchical model. The hierarchical stage is automatically mapped to a pseudo column LEVEL. When a query with the CONNECT BY clause is created, the pseudo column LEVEL is added to the query output automatically. The query will fail if you try to print the value of the pseudo column LEVEL in a query that does not include the CONNECT BY clause. In this case, Oracle will generate an error.

The Problem

If a query attempts to print the pseudo column LEVEL without using the CONNECT BY clause, the query will fail. The level is printed in the query in the example below. However, the query does not include the CONNECT BY clause. As a result, the oracle throws an error.

select level from dual;

Error

ORA-01788: CONNECT BY clause required in this query block
01788. 00000 -  "CONNECT BY clause required in this query block"
*Cause:    
*Action:
Error at Line: 24 Column: 19

Solution 1

If the query is not created using the CONNECT BY clause, add the CONNECT BY clause in the query. The CONNECT by clause will create the pseudo column LEVEL in the query output automatically. The level will be printed in the sql query output.

Error

select level from dual;

Solution

select level from dual CONNECT BY level<=5;

Output

LEVEL
-----
1
2
3
4
5

Solution 2

Remove the level column from the query if the pseudo column level is added without using the CONNECTED BY clause. The query output will not include the level column. Other database table columns can be printed as well. By removing the pseudo column level from the sql query output, the error will be resolved.

Error

select level from dual;

Solution

select * from dual;

Output

DUMMY
-----
X

Solution 3

If you print the pseudo column level and the subquery uses the CONNECT BY clause, the main query could not print the pseudo column. You should make sure to print the pseudo column level only in the sql query which is directly used by the CONNECT BY clause.

Error

SELECT level 
FROM
    (select level from dual connect by Level < 10),
    (select level from dual connect by Level < 10);

Solution

select level1, level2, ((level1-1)*10) + (level2-1) + 1 as id from
  (select level as level1 from dual connect by level <= 10),
  (select level as level2 from dual connect by level <= 10) ;