title: Can I nest functions in Hibernate custom dialect?
body: <p>I have a hibernate model that needs to support multiple databases (MySQL, Oracle, Sybase etc) and has some date logic in it. Going over various forums / posts I came across the idea of creating custom dialects for Hibernate and registering a function, with database-specific logic for each dialect. These worked beautifully (showing just the relevant bits - apologies in advance, I had to obfuscate some of the values and field names for the post):</p>
  
  <pre><code>public MyOracle10gDialect() {
      super();
      SQLFunctionTemplate sqlFunctionTemplate = 
              new SQLFunctionTemplate(Hibernate.DATE, "SYSDATE - ?1");
  
      registerFunction("date_sub_days", sqlFunctionTemplate);
  }
  
  public MyMySQLDialect() {
      super();
      SQLFunctionTemplate sqlFunctionTemplate = 
              new SQLFunctionTemplate(Hibernate.DATE, "DATE_SUB(UTC_TIMESTAMP(),INTERVAL ?1 DAY)");
      registerFunction("date_sub_days", sqlFunctionTemplate);
  }
  </code></pre>
  
  <p>Now however I need to use a function to resolve the parameter to pass into this function. Unfortunately I don't have access to the code that uses this model otherwise I would just move the logic into code, but is there any way to do nested functions? I want to use 'GREATEST(x, y ,z)' as the parameter to my 'date_sub_days' function. I basically want this to work and am failing:</p>
  
  <pre><code>WHERE date_sub_days(GREATEST(:TRADE_HISTORY_MAX_DAYS,:ORDER_HISTORY_MAX_DAYS,:NEG_HISTORY_MAX_DAYS)) &amp;lt; value1
  </code></pre>
  
  <p>because it doesn't resolve the 'greatest' function into a single parameter. </p>
  
  <p>In MySQL I get the following printed to console:</p>
  
  <pre><code>Hibernate: 
  select blah from blah where
      DATE_SUB(UTC_TIMESTAMP(),INTERVAL GREATEST(? DAY) &lt; value1 
  ERROR util.JDBCExceptionReporter - Parameter index out of range (2 &gt; number of parameters, which is 1).
  </code></pre>
  
  <p>And in Oracle I get the following printed to console:</p>
  
  <pre><code>Hibernate: 
  select blah from blah where 
  SYSDATE - GREATEST(?&lt;value1
  ERROR util.JDBCExceptionReporter - Invalid column index
  </code></pre>
  
  <p>Would love to hear suggestions on how to implement this in either the dialect or a change in the HQL.</p>