if select statement contains "CONNECT BY" clause, QBE fails

[Serge]

Hello,

i'm using last version of DOA doa41d6
if SQL statemnt contains "CONNECT BY", using QBE in TOracleDataSet gives errors.

This is example:
original SQL:

select a.rowid, a.*
from pay a
START WITH a.ref is not null
CONNECT BY PRIOR pay_id = a.parentpay_id
ORDER SIBLINGS BY a.ref

now using QBE i want to find record with field "ref" = 12:

select a.rowid, a.*
from pay a
START WITH a.ref is not null
CONNECT BY PRIOR pay_id = a.parentpay_id
where (REF = 12)
ORDER SIBLINGS BY a.ref
/* Modified for QBE */

Execution time: 0 ms

Oracle error occurred: 933 (ORA-00933: SQL command not properly ended)

Look at SplitSelect procedure in OracleData.pas.
it does not know about "START WITH" or "CONNECT BY" SQL clauses. As result if there is no where clause in original SQL, QBE adds "WHERE" after "CONNECT BY" and i receive the error.

If SQL contains "WHERE":

select a.rowid, a.*
from pay a
where a.pay_id<1000
START WITH a.ref is not null
CONNECT BY PRIOR pay_id = a.parentpay_id
ORDER SIBLINGS BY a.ref

we receive:

select a.rowid, a.*
from pay a
where (a.pay_id<1000
START WITH a.ref is not null
CONNECT BY PRIOR pay_id = a.parentpay_id)
and
((REF = 12))
ORDER SIBLINGS BY a.ref
/* Modified for QBE */

Execution time: 0 ms

Oracle error occurred: 907 (ORA-00907: missing right parenthesis)

is it bug of DOA, or i can somehow tune SQL so that it could work?

 

[Marco Kalter]

There is indeed no QBE support for hierarchical queries. I have added this to the list of enhancement requests.