title: IBM Informix SQL Syntax assistance
body: <p>Wondering if anyone may be able to help me with some SQL here.  I'm tasked with retrieving some data from a legacy DB system - It's an IBM Informix DB running v7.23C1.  It may well be that what I'm trying to do here is pretty simple, but for the life of me I can't figure it out.</p>
  
  <p>I'm used to MS SQL Server, rather than any other DB system and this one seems quite old: <a href="http://publib.boulder.ibm.com/epubs/pdf/3731.pdf" rel="nofollow">http://publib.boulder.ibm.com/epubs/pdf/3731.pdf</a> (?)</p>
  
  <p>Basically, I just want to run a query that includes nesting, but I can't seem to figure out how to do this.  So for example, I have a query that looks like this:</p>
  
  <pre><code>SELECT cmprod.cmp_product, 
   (stock.stk_stkqty - stock.stk_allstk) stk_bal, 
      stock.stk_ospurch, 
      stock.stk_backord,
      'Current Sales Period',
      'Current Period -1',
      'Current Period -2',
      cmprod.cmp_curcost,
      stock.stk_credate,
      stock.stk_lastpurch,
      stock.stk_binref
  FROM informix.stock stock,
       informix.cmprod cmprod
  WHERE stock.stk_product = cmprod.cmp_product
     AND (cmp_category = 'VOLV'
     OR cmp_category = 'VOLD'
     OR cmp_category = 'VOLA')
     AND stk_loc = 'ENG';
  </code></pre>
  
  <p>Now, basically where I have values like 'Current Period -1' I want to include a nested field which will run a query to get the sales within a given date range.  I'm sure I can put those together separately, but can't seem to get the compiler to be happy with my code when executed altogether.</p>
  
  <p>Probably something like (NB, this specific query is for another column, but you get the idea):</p>
  
  <pre><code>SELECT s.stmov_product, s.stmov_trandate, s.stmov_qty
  FROM informix.stmove s
  WHERE s.stmov_product = '1066823'
  AND s.stmov_qty &gt; 0
  AND s.stmov_trandate IN (
       SELECT MAX(r.stmov_trandate) 
       FROM informix.stmove r
       WHERE r.stmov_product = '1066823'
       AND r.stmov_qty &gt; 0)
  </code></pre>
  
  <p>What makes things a little worse is I don't have access to the server that this DB is running on.  At the moment I have a custom C# app that connects via an ODBC driver and executes the raw SQL, parsing the results back into a .CSV.</p>
  
  <p>Any and all help appreciated!</p>