SEO

I'm writing a report where in I need a set of sub-totals. The only
way I know how to get there is with multiple selects, in ISQL, but
apparently they're not flying. I have this above the format section
and its not working:

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
order by pa_prechk_date, pa_cp_py_code
end

select sum(pa_amt_paid) docsum
where pa_cp_py_code = "DOC"
and pa_prechk_date between $bdate and $edate
end

select sum(pa_amt_paid) possum
where pa_cp_py_code = "POS"
and pa_prechk_date between $bdate and $edate
end

select sum(pa_amt_paid) dlqsum
where pa_cp_py_code = "DLQ"
and pa_prechk_date between $bdate and $edate
end

select sum(pa_amt_paid) trusum
where pa_cp_py_code = "TRQ"
and pa_prechk_date between $bdate and $edate
end

Any suggestions on how to get this to run?

Thanks in advance!

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.

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

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

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