General syntax error raised from CONNECT BY query in Informix when using quoted table identifiers

Question

When running the following query on an Informix database, the database reports a general syntax error (without any indication with respect to what causes the problem). The same query runs perfectly on CUBRID or Oracle databases, both of which also support the CONNECT BY syntax:

select 
  lower(connect_by_root "t_directory"."name"), 
  connect_by_isleaf, 
  connect_by_iscycle, 
  substr(
    sys_connect_by_path(lower("t_directory"."name"), '/'), 
    2) "dir"
from "t_directory"
start with "t_directory"."parent_id" is null
connect by nocycle prior "t_directory"."id" = "t_directory"."parent_id"
order siblings by lower("t_directory"."name") asc

The database I'm using is a Developer Edition of Informix 12.10 on Windows. I'm running the query from a JDBC driver with the following connection URL (to allow for quoted table identifiers):

jdbc:informix-sqli://localhost:9092/test:INFORMIXSERVER=ol_informix;DELIMIDENT=y

score 0 · Accepted Answer · answered Jul 30 '14 at 12:07

The exact issue here is the fact that prior doesn't accept quoted table identifiers, although quoted column identifiers seem to be fine. This query runs perfectly well:

select 
  lower(connect_by_root "t_directory"."name"), 
  connect_by_isleaf, 
  connect_by_iscycle, 
  substr(
    sys_connect_by_path(lower("t_directory"."name"), '/'), 
    2) "dir"
from "t_directory"
start with "t_directory"."parent_id" is null
connect by nocycle prior t_directory."id" = "t_directory"."parent_id"
order siblings by lower("t_directory"."name") asc

... with the difference being:

-- Bad:
connect by nocycle prior "t_directory"."id" = "t_directory"."parent_id"

-- Good:
connect by nocycle prior t_directory."id" = "t_directory"."parent_id"

General syntax error raised from CONNECT BY query in Informix when using quoted table identifiers

1 Answers1