In message <cbde66ac.0404120627.6031d04c@posting.google.com >, Leo
<leonidesruiz@hotmail.com> writes
>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.

It would have been useful to know it was an ACE report, not an SQL
report originally.

Yes you need an END after the *last* SELECT clause, but the errors below
are telling you what is wrong.

If you have more than one SELECT, each one needs to be run into a TEMP
table, and you need to finally have got what you need from those TEMP
tables.

As far as I remember you *do* need a semicolon after each SELECT that is
run into a TEMP table.

However I'm intrigued that the 1st statement has 4 values for
pa_cp_py_code, and the second only has 1.



>
>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

--
Five Cats
Email to: cats_spam at uk2 dot net