Use SUBSTR in HSQL left outer join clause

Question

I need to perform the following query using HSQL (with ORACLE works fine):

select t.name as sh_name,
         b.name as sh_bName
from PERSON t 
       left outer join TRANS tr on t.id=tr.tid
       left outer join AGREEM agr on tr.tripNumber=SUBSTR(agr.coll_external,1,4)
       left outer join PERSON b on (b.TYP='BROKER' and agr.broker_id = b.id)

where t.TYP='TRADER'

I receive the following error: not allowed in OUTER JOIN condition in statement. The problem comes from the usage of SUBSTR, without it I don't receive any error. In Oracle the above statement works just fine.

Any idea how I can use SUBSTR in a join clause with HSQL?

score 0 · Accepted Answer · answered Feb 27 '13 at 14:03

0

This is a limitation of older versions of HSQLDB.

Newer versions 2.x allow the use of SUBSTR or any other function in OUTER JOIN conditions.

The latest versions also support a compatibility mode for improved handling of Oracle data types and features.

answered Feb 27 '13 at 14:03

fredt

24,044
3
40
61

So a newer version of hsqldb would be the solution... Tanks! – Marius Feb 27 '13 at 14:19

Use SUBSTR in HSQL left outer join clause

1 Answers1