title: Use SUBSTR in HSQL left outer join clause
body: <p>I need to perform the following query using HSQL (with ORACLE works fine):</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>I receive the following error: <code>not allowed in OUTER JOIN condition in statement</code>. The problem comes from the usage of <code>SUBSTR</code>, without it I don't receive any error. In Oracle the above statement works just fine. </p>
  
  <p>Any idea how I can use <code>SUBSTR</code> in a join clause with HSQL?</p>