As its an ACE report it's dead easy (v7.20).


Remove all your secondary SELECT statements, leaving just your basic select.

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


Add these to your LAST ROW section

Print "DOC :", (total of pa_amt_paid where pa_cp_py_code = "DOC")
Print "POS :", (total of pa_amt_paid where pa_cp_py_code = "POS")
Print "DLQ :", (total of pa_amt_paid where pa_cp_py_code = "DLQ")
Print "TRU :", (total of pa_amt_paid where pa_cp_py_code = "TRU")


Wayne Sheldon
IT Manager
Airflow Streamlines


-----Original Message-----
From: leonidesruiz@hotmail.com [mailto:leonidesruiz@hotmail.com]
Sent: 12 April 2004 15:28
To: informix-list@iiug.org
Subject: Re: Multiple selects in ISQL


Five Cats <cats_spam@[127.0.0.1]> wrote in message
news:<icifuIsfvtdAFwm4@[127.0.0.1]>...
> In message <cbde66ac.0404090807.112e8b1@posting.google.com> , Leo
> <leonidesruiz@hotmail.com> writes
> >Any suggestions on how to get this to run?
>
> select from what?
>
> You need to have a FROM clause in each SELECT statement. Also what is
> the 'END' doing in there? Informix SQL uses a semicolon ; as a
> delimiter between SQL statements.
Here is the message I get when I use the semi-colon between select
statements. I'd removed some the lines, not a good idea, to save some
space in the post. I've asked around ad I've been told that a single
SELECT statement is a limitation of ISQL. I'm hoping that's wrong but
this error mesage is confusingly cryptic to me. The sub-totals ned to
be at the very end. I want the data in date order but I want a
summary page at the end of the report with not only the total for all
of the fees but a break-down, sub-totals, of the each different type.

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;
#
#A select statement which is not the final select statement in an
# ACE report must select into a temporary table.
# See error number -9059.
#

select sum(pa_amt_paid) docsum
#_______^
#
# A grammatical error has been found on line 32, character 9.
# The construct is not understandable in its context.
# See error number -8018.
#
from flmastfile, flmastpymt
where ms_cgn = pa_cgn
and pa_prechk_date between $bdate and $edate
and pa_cp_py_code = "DOC"
and ms_4d_flag = "Y"
and (pa_conv_void_code is null or
pa_conv_void_code <> "V");

end

format

page header
print column 1, "Miami-Dade County Clerk of Courts",
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 16,
column 44,
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


DISCLAIMER: Rob Hunt, Alistair Grove and David Langton have been appointed
as joint administrators to Airflow Streamlines Plc and Harris (Northampton)
Haulage Limited. The affairs, business and property of the company are
being managed by the joint administrators.


sending to informix-list