title: How to simulate GREATEST() in Sybase ASE?
body: <p>Most databases have something like a <a href="http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#function_greatest" rel="nofollow noreferrer"><code>GREATEST</code></a> function, which can be useful some times. At least these databases don't have such a function:</p>
  
  <ul>
  <li>Derby</li>
  <li>SQL Server</li>
  <li>Sybase ASE</li>
  <li>Sybase SQL Anywhere</li>
  </ul>
  
  <p>For SQL Server and Sybase SQL Anywhere, the function can be simulated using subqueries and <code>UNION ALL</code>, as can be seen in <a href="https://stackoverflow.com/questions/4725823/function-in-sql-server-2008-similar-to-greatest-in-mysql">this question here</a>. An example:</p>
  
  <pre><code>-- SELECT GREATEST(field1, field2, field3) FROM my_table
  SELECT (SELECT MAX(c) FROM 
                       (SELECT my_table.field1 AS c UNION ALL 
                        SELECT my_table.field2      UNION ALL
                        SELECT my_table.field3) T) AS greatest
  FROM my_table
  </code></pre>
  
  <p>But this doesn't work in Sybase ASE. Apparently, the subqueries don't have access to the outer query's <code>my_table</code> reference. The error I get is</p>
  
  <blockquote>
    <p>The column prefix 'my_table' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead</p>
  </blockquote>
  
  <p>Note, this problem does not appear with Sybase SQL Anywhere. Any idea what's wrong here and how I could re-write the query?</p>
  
  <p>I'd like to avoid</p>
  
  <ul>
  <li>Stored functions, as I may not have the necessary grants to create them</li>
  <li>Lengthy <code>CASE</code> expressions, as the expression length of the combined permutation of all comparisons needed with nested <code>CASE</code> expressions is at least <code>O(n^2)</code> when <code>n</code> is the number of parameters for <code>GREATEST</code></li>
  </ul>