Need to better understand "invalid qualifier" error 1

[LarrySteele]

I'm trying to borrow a sql statement from:

http://www.oracle.com/technology/oramag/oracle/05-may/o35asktom.html.

The specific statement I'm using from the page is:

SELECT a,
     TO_NUMBER(
      SUBSTR(scbp,1,
             INSTR(scbp,'.')-1)
      ) b
    FROM (
  SELECT a,
    LTRIM(
      SYS_CONNECT_BY_PATH(a,'.'),
         '.') ||'.' scbp
    FROM test_table
   CONNECT BY PRIOR b=a
         )
   ORDER BY 2

Each time I try to run this, Oracle returns an invalid qualifier error 'b' on line 'CONNECT BY PRIOR b=a'.

Is there a problem with the way the query is written? It's straight from the web site, but I can't get his query to run. I think what's really eating me is that I'm presuming that the author ran the query posted to retrieve the output posted and I just want to know why I can run what he presumably ran.

As always, help is greatly appreciated,
Larry

 

[taupirho]

What version of Oracle are you on?. Some have had problems with sys_connect_by_path construct on releases less than than 9.2.0.2


In order to understand recursion, you must first understand recursion.

 

[LarrySteele]

I'm on 9i.

- Larry

 

[taupirho]

Post the reuslts of the following query on your system please. That will tell you you're proper version

select * from v$version



In order to understand recursion, you must first understand recursion.

 

[LarrySteele]

Looks like I was misinformed:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

Since we're apparently using 10g, it opens up other avenues for what I'm trying to do.

Still, I would like to know about the invalid identifier problem.

 

[LarrySteele]

Wow, you ever have one of those days?

I just realized my problem. The columns in test_table are a and b, NOT a and scbp. Once I renamed the column in test_table, the query ran.

I'm SOOOOOO glad it's Friday

Thanks taupirho for the script. At least I now know the version we're running!