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/

John,
This is an ACE report. I'll post the entire code below so that we're
on the same page. The report as it stands is perefct except it lacks
a sub-total for each fee type at the end just above the grand total
for all of the fees. When I added semi-colons between SELECT
statements, it gave an error regarding submiting to TEMP tables. Your
suggestion sounds great but doesn't quite work either, but I'm sure
that's a syntax error on my part. Here is the report in its entirety.
BTW, your help is greatly appreciated.

database
family
end

define
variable bdate date
variable edate date
end

input
prompt for bdate using "Enter Period Start Date: MMDDYYYY "
prompt for edate using "Enter Period End Date: MMDDYYYY "
end

output
report to "/reports/prechkfees.rpt"
left margin 1
right margin 80
page length 66
end

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", "DLQ", "TRU")
and ms_4d_flag = "Y"
and (pa_conv_void_code is null or
pa_conv_void_code <> "V")
order by pa_prechk_date

end

format

page header
print column 1, "Clerk",
column 56, "Report Date ", today using "mm dd yyyy"
print column 1, "Pre-Check Clerk Fees for DLQ, POS, DOC and INT"
print column 1, "Beginning date ",
column 16, bdate using "mm dd yyyy",
column 30, "Ending Date ",
column 42, edate using "mm dd yyyy"
skip 1 lines
print, column 44, "----------- Pre-Check Info ------------"
print, "Case Number",
column 16,
column 44,
column 56, "Date",
column 66, "Type",
column 74, "Amount"
skip 1 lines

on every row print
column 1, ms_case_number,
column 56, pa_prechk_date using "mmddyyyy",
column 66, pa_cp_py_code,
column 70, pa_amt_paid using "###,###.##"

after group of pa_prechk_date
skip 1 line
print column 34, "Sub-total for",
column 50, pa_prechk_date ,
column 70, group total of pa_amt_paid
using "###,###.##"
skip 1 line

on last row
skip 1 lines
print column 45, "Total Clerk Fees",
column 70, total of pa_amt_paid using "###,###.##"

page trailer
skip 1 lines
print column 35, "Page ", pageno using "<<<<"
end

Leo wrote:
> This is an ACE report. [...]

I think I'm correct in saying Leo now has a workable (albeit not
desperately elegant) solution to his problem. It involved using
conditional overall aggregates (TOTAL OF ... WHERE ...) in the ON LAST
ROW block. Given that he knows the 4 payment type codes, it is not
too bad; if the list was variable, the coding in ACE would be awful.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/