ISQL help

I have a report that will need sub-totals for case types as well as a total for all of the fees generated for a date range. I beleive ISQL has a limitation on select statemts. Here is what I'd like to do but can't. Does anyone know of a workaround/solution? select ms_case_number, pa_cp_py_code, pa_prechk_date, pa_amt_paid from flmastfile, flmastpymt where ms_cgn = pa_cgn and pa_prechk_date between $bdate and $edate and pa_cp_py_code in ("DOC", "POS") order by pa_prechk_date, pa_cp_py_code select sum(pa_amt_paid) docsum where pa_cp_py_code = "DOC" and pa_prechk_date between $bdate and $edate select sum(pa_amt_paid) possum where pa_cp_py_code = "POS" and pa_prechk_date between $bdate and $edate

Leo wrote: > I have a report that will need sub-totals for case types as well as a > total for all of the fees generated for a date range. I beleive ISQL > has a limitation on select statemts. Here is what I'd like to do but > can't. Does anyone know of a workaround/solution? > > select ms_case_number, pa_cp_py_code, pa_prechk_date, > pa_amt_paid from flmastfile, flmastpymt > where ms_cgn = pa_cgn > and pa_prechk_date between $bdate and $edate > and pa_cp_py_code in ("DOC", "POS") > order by pa_prechk_date, pa_cp_py_code> > select sum(pa_amt_paid) docsum > where pa_cp_py_code = "DOC" > and pa_prechk_date between $bdate and $edate > > select sum(pa_amt_paid) possum > where pa_cp_py_code = "POS" > and pa_prechk_date between $bdate and $edate As already noticed in response to another question, you need a from clause in each of these queries. You also need a semi-colon to separate them. Now - you're using ISQL, you say, so this is an ACE report? And $bdate and $edate are variables specified either in the INPUT section or as PARAM values? It would be helpful to have a complete description of the tools you're using so we don't have to guess. ACE is pretty good at subtotalling things - that's what AFTER GROUP OF clauses are for. So, there's a fair chance you don't need to do anything extra in the SQL part. On the face of it, you actually don't even want grouped totals; you just want the grand total of "DOC" payments and "POS" payments? So, in the ON LAST ROW section, you write: PRINT (SUM(pa_amt_paid) WHERE pa_cp_py_code = "POS") USING "$$$$,$$&.&&", COLUMN 20, (SUM(pa_amt_paid) WHERE pa_cp_py_code = "DOC") USING "$$$$,$$&.&&" Or whatever... Note that since the data being totalled is already restricted to the right date range, you don't need to repeat the date range in the SUM operators. If you wanted the totals per ms_case_number, then you'd use an AFTER GROUP OF ms_case_number clause and print the GROUP SUM(pa_amt_paid) values. So, most likely, your initial single select statement will do. If you need more help, explain more exactly what you're after. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/