title: Error message "function name is missing )." in visual foxpro 9.0
body: <p>I am having trouble converting a SQL statement to FoxPro. The trouble I'm having is that I get the error code " Function name is missing ). ", but I just can't figure out what or where the problem is. The trouble is in the line below. Any help is greatly appreciated.</p>
  
  <p><code>NVL(select sum(amount) from tpaymentitem where tpaymentitem.invoiceid = 0 and tpaymentitem.programid = (select max(tinvoiceitem.programid) FROM tinvoiceitem),0) as prepay,;</code></p>
  
  <p>The whole foxpro code is below.</p>
  
  <pre><code>Select  acc.accountid,;
  ti.invoiceid,;
  ti.invoicedate,;
  DATE() - ti.invoicedate as invage,;
  sum(NVL([subtotalregular],0) + NVL([fedtaxregular],0) + NVL([statetaxregular],0) + NVL([localtaxregular],0)) as invamount, ;
  NVL((select sum(amount) from tpaymentitem where invoiceid = ti.invoiceid),0) as paidamt,;
  (sum(NVL([subtotalregular],0) + NVL([fedtaxregular],0) + NVL([statetaxregular],0) + NVL([localtaxregular],0)) - NVL((select sum(amount) from tpaymentitem where invoiceid = ti.invoiceid),0)) as invbalance,;
  max(tii.programid) as programid,;
  NVL(select sum(amount) from tpaymentitem where tpaymentitem.invoiceid = 0 and tpaymentitem.programid = (select max(tinvoiceitem.programid) FROM tinvoiceitem),0) as prepay,;
  do while com.stchargeinterest = 1 AND acc.nofincharge = 0 ;
    if DATE() - ti.invoicedate &gt;= com.stmindaysforinterest ;
      ROUND((sum(NVL([subtotalregular],0) + NVL([fedtaxregular],0) + NVL([statetaxregular],0) + NVL([localtaxregular],0)) - NVL((select sum(amount) from tpaymentitem where invoiceid = ti.invoiceid),0)) * ((com.stinterestrate / 365) * DATE() - ti.invoicedate)),2);
    ENDif  ;
  ENDdo as financecharge,;
  com.stchargeinterest,;
  com.stinterestrate,;
  acc.nofincharge,;
  com.stmindaysforinterest,;
  com.stinterval;
  INTO cursor tmpbalance;
  FROM taccount acc;
  INNER JOIN tcompany com ON com.companyid=acc.companyid;
  LEFT JOIN tinvoice ti ON ti.accountid=acc.accountid;
  LEFT JOIN tinvoiceitem tii ON ti.invoiceid=tii.invoiceid;
  WHERE acc.accountid = m.cust_no AND acc.companyid = 1;
  GROUP BY acc.accountid,ti.invoiceid,ti.invoicedate,com.stinterval,com.stchargeinterest ,;
  com.stinterestrate,acc.nofincharge,com.stmindaysforinterest,acc.companyid
  </code></pre>
  
  <p>The SQL code I'm converting from is below and was originally captured from a stored procedure.</p>
  
  <pre><code>Select  acc.accountid,ti.invoiceid,ti.invoicedate,DateDiff(d,ti.invoicedate,getdate()) as invage,
  sum(isnull([subtotalregular],0)+isnull([fedtaxregular],0)+isnull([statetaxregular],0)
      +isnull([localtaxregular],0)) as invamount, 
  ISNULL((select sum(amount) from tpaymentitem where invoiceid=ti.invoiceid),0) as paidamt,
  (sum(isnull([subtotalregular],0)+isnull([fedtaxregular],0)+isnull([statetaxregular],0)
      +isnull([localtaxregular],0)) -
  ISNULL((select sum(amount) from tpaymentitem where invoiceid=ti.invoiceid),0)) as invbalance,
  max(tii.programid) as programid,
  ISNULL((select sum(amount) from tpaymentitem where invoiceid=0 and programid = max(tii.programid)),0) as prepay,
  CASE WHEN (com.stchargeinterest = 1) AND (acc.nofincharge=0) THEN
      CASE WHEN DateDiff(d,ti.invoicedate,getdate()) &gt;= com.stmindaysforinterest THEN
          ROUND((sum(isnull([subtotalregular],0)+isnull([fedtaxregular],0)+isnull([statetaxregular],0)+isnull([localtaxregular],0)) 
              -ISNULL((select sum(amount) from tpaymentitem where invoiceid=ti.invoiceid),0)) 
              * ((com.stinterestrate / 365) * DateDiff(d,ti.invoicedate,getdate())),2)
      ELSE  0 END 
  ELSE 0 END as financecharge,com.stchargeinterest ,com.stinterestrate,
  acc.nofincharge,com.stmindaysforinterest,com.stinterval
  INTO #tmpbalance
  FROM taccount acc
  INNER JOIN tcompany com ON com.companyid=acc.companyid
  LEFT JOIN tinvoice ti ON ti.accountid=acc.accountid
  LEFT JOIN tinvoiceitem tii ON ti.invoiceid=tii.invoiceid
  WHERE acc.accountid= @accountid AND acc.companyid = @companyid
  GROUP BY acc.accountid,ti.invoiceid,ti.invoicedate,com.stinterval,com.stchargeinterest ,
  com.stinterestrate,acc.nofincharge,com.stmindaysforinterest,acc.companyid
  </code></pre>