Why Doesn't This Work?

[Speed]

We are upgrading our Oracle server to 10g. The code below works with previous versions of Oracle, but with 10g it generates the following error:

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

Here's the code:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
select level, classn_id, classn_classn_id, classn_type, code, description
from mas.classifications
where classn_classn_id is null
union
select 0,0,0,'0','0','General Explanatory Notes' from dual
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Does anyone have any suggestions?

Thanks.

Regards,

Steve

 

[Devil]

level is reserved word in oracle, who not recommended used it is.
try use double quoted "level" for your table or rename your column:

select "level", ...
from mas.classifications

P.S.: By the way Description is also reserved word

 

[Devil]

Please, ask my question:
How are you create table with column named as level?
I try crete some table as:
create table leveltest(level number);
and get error:
ORA-00904: : invalid identifier
But this code work:
create table loans.leveltest("level" number);
So, if you create table in second case then you must also use double quoted notation or refuse for used reserved word level and rename on other.

 

[Speed]

Thanks for the response Devil. I didn't write this code - I've taken over maintenance of this system. It seems level is being used legitimately, ie. it isn't the name of a column in the table, it's actually referencing the level keyword. The table contains a hierarchical structure.

 

[phocai]

LEVEL is a pseudocolumn used to return a node level for each row returned by a hierarchical query. To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

 

[Speed]

Thanks Phocai. Yeah, I sort of realized this when I looked up the explanation for the error message. It doesn't explain why it has worked fine for years with previous versions of Oracle, eg. 8i. We have 8i running on one server and 10g on another. It works fine under 8i but throws the error message under 10g.

I really don't know how to change the query to include these clauses so that it will work correctly. I have referred this to the Oracle guru here to see if he can work it out.