使用多个SELECT INTO语句编译ACE报告时出错
问题描述：
INFORMIX-SQL 4.10:
INFORMIX-SQL 4.10:
好的，因此在修复INTO TEMP语法并使用AS别名之后，ACE编译器抱怨说GROUP BY子句中没有每个别名，因此我将其添加到每个SELECT语句中.但是，现在我仍然在FORMAT语句上看到GRAM ERR(请参阅更新的代码示例)
OK, So after fixing the INTO TEMP syntax and using AS aliases, the ACE compiler complained about not having every single alias in a GROUP BY clause so I added it to each SELECT statement. However now I still get a GRAM ERR on the FORMAT statement (see updated code sample)
database PAWNSHOP
END
define
variable sfecha date
variable efecha date
end
input
prompt for sfecha using "DESDE FECHA: "
prompt for efecha using "HASTA FECHA: "
end
output
report to printer
top margin 0
bottom margin 0
page length 33
left margin 0
right margin 80
end
select count(*) AS rcount,
pwd_trx_date AS rtrxdate,
pwd_trx_type AS rtrxtype,
pwd_last_type AS rlasttype,
pwd_last_amt AS rlastamt,
pwd_pawn_amt AS rpawnamt,
pwd_cob1 AS rcob1,
pwd_cob2 AS rcob2,
pwd_cob3 AS rcob3,
pwd_cob4 AS rcob4
from boletos
where pwd_trx_date >= $sfecha
and pwd_trx_date <= $efecha
and pwd_trx_type = "E"
and pwd_last_type = "R"
group
by rtrxdate,
rtrxtype,
rlasttype,
rlastamt,
rpawnamt,
rcob1,
rcob2,
rcob3,
rcob4
into
temp r;
select count(*) AS icount,
pwd_trx_date AS itrxdate,
pwd_trx_type AS itrxtype,
pwd_last_type AS ilasttype,
pwd_last_amt AS ilastamt
from boletos
where pwd_trx_date >= $sfecha
and pwd_trx_date <= $efecha
and pwd_trx_type = "E"
and pwd_last_type = "I"
group
by itrxdate,
itrxtype,
ilasttype,
ilastamt
into
temp i;
select count(*) AS fcount,
pwd_trx_date AS ftrxdate,
pwd_trx_type AS ftrxtype,
pwd_last_type AS flasttype,
pwd_last_amt AS flastamt,
pwd_pawn_amt AS fpawnamt
from boletos
where pwd_trx_date >= $sfecha
and pwd_trx_date <= $efecha
and pwd_trx_type IN ("E","C","P")
and pwd_last_type = "F"
group
by ftrxdate,
ftrxtype,
flasttype,
flastamt,
fpawnamt
into
temp f;
select count(*) AS pcount,
pwd_trx_date AS ptrxdate,
pwd_trx_type AS ptrxtype,
pwd_last_type AS plasttype,
pwd_last_amt AS plastamt,
pwd_pawn_amt AS ppawnamt
from boletos
where pwd_trx_date >= $sfecha
and pwd_trx_date <= $efecha
and pwd_trx_type = "P"
and pwd_last_type = "R"
group
by ptrxdate,
ptrxtype,
plasttype,
plastamt,
ppawnamt
into
temp p;
select count(*) AS ecount,
pwd_trx_date AS etrxdate,
pwd_trx_type AS etrxtype,
pwd_last_type AS elasttype,
pwd_last_amt AS elastamt,
pwd_pawn_amt AS epawnamt
from boletos
where pwd_trx_date >= $sfecha
and pwd_trx_date <= $efecha
and pwd_trx_type = "E"
and pwd_last_type = "E"
group
by etrxdate,
etrxtype,
elasttype,
elastamt,
epawnamt
into
temp e;
select count(*) AS ccount,
pwd_trx_date AS ctrxdate,
pwd_trx_type AS ctrxtype,
pwd_last_type AS clasttype,
pwd_pawn_amt AS cpawnamt
from boletos
where pwd_trx_date >= $sfecha
and pwd_trx_date <= $efecha
and pwd_trx_type = "C"
and pwd_last_type = "C"
group
by ctrxdate,
ctrxtype,
clasttype,
cpawnamt
into
temp c
end
format
**^
GRAM ERR UNDESIREABLE CONSTRUCT**
after group of
rtrxdate,
rtrxtype,
rlasttype,
rlastamt,
rpawnamt,
rcob1,
rcob2,
rcob3,
rcob4
print column 1,"CANTIDAD INGRESOS TOTAL GANANCIA"
print column 1,"-------- --------- ------- --------"
print column 2,group total of rcount using "###,###",
column 10,"RETIROS",
column 20,group total of rlastamt "###,###",
column 42,(
(group total of rcob1) +
(group total of rcob2) +
(group total of rcob3) +
(group total of rcob4)
) -
(group total of rpawnamt) using "###,###"
after group of
itrxdate,
itrxtype,
ilasttype,
ilastamt
print column 2,group total of icount using "###,###",
column 10,"INTERESES",
column 20,group total of ilastamt using "###,###",
column 42,group total of ilastamt using "###,###"
after group of
ftrxdate,
ftrxtype,
flasttype,
flastamt,
fpawnamt
print column 2,group total of fcount using "###,###",
column 10,"FUNDIDOS",
column 20,group total of flastamt using "###,###",
column 42,(group total of flastamt) -
(group total of fpawnamt) using "###,###"
after group of
ptrxdate,
ptrxtype,
plasttype,
plastamt,
ppawnamt
print column 2,group total of pcount using "###,##&",
column 10,"PLATERIA",
column 20,group total of plastamt using "###,###",
column 42,group total of plastamt using "###,###"
after group of
etrxdate,
etrxtype,
elasttype,
elastamt,
epawnamt
skip 2 lines
print column 1,"CANTIDAD EGRESOS TOTAL "
print column 1,"-------- --------- ------- "
print column 2,group total of ecount using "###,###",
column 10,"PRESTAMOS",
column 20,group total of elastamt using "###,###"
after group of
ctrxdate,
ctrxtype,
clasttype,
cpawnamt
print column 2,group total of ccount using "###,###",
column 10,"COMPRAS ",
column 20,group total of clastamt using "###,###"
end
答
好吧，我通过使用ORDER BY与GROUP BY来解决了这个问题.请注意，列必须以相反的顺序放置在SELECT语句的ORDER BY子句中.以下ACE报告完成了这一挑战:
Well, I solved the problem by using ORDER BY vs. GROUP BY. Notice that the columns must be placed in reverse order in the ORDER BY clause of the SELECT statement. The following ACE report accomplished the challenge:
database PAWNSHOP
END
define
variable sfecha date
variable efecha date
variable dummy integer
end
input
prompt for sfecha using "DESDE FECHA: "
prompt for efecha using "HASTA FECHA: "
end
output
{report to printer}
top margin 0
bottom margin 0
page length 24
left margin 0
right margin 80
end
select trxdate,
trxtype,
trxcode,
trxamt,
trxprofit
from trx
where trxdate >= $sfecha
and trxdate <= $efecha
order by trxcode,trxtype,trxdate
end
format
page trailer
pause
page header
skip 2 lines
print column 21,"Transacciones del sistema viejo y sistema nuevo."
print column 21,"Totales desde ",sfecha using "mmm-dd-yy",
" hasta ",efecha using "mmm-dd-yy"
skip 1 line
print column 1,
" CONTEO TOTAL GANANCIA"
print column 1,
" ------ ------- --------"
after group of trxtype
if trxtype = "E" and trxcode = "R" then
begin
print column 13,group count using "###,##&",
column 21,"Empenos Retirados",
column 42,group total of trxamt using "###,##&",
column 64,group total of trxprofit using "###,##&"
end
if trxtype = "E" and trxcode = "I" then
begin
print column 13,group count using "###,##&",
column 21,"Pagos de Intereses",
column 42,group total of trxamt using "###,##&",
column 64,group total of trxamt using "###,##&"
end
if trxtype = "E" and trxcode = "F" then
begin
print column 13,group count using "###,##&",
column 21,"Empenos Fundidos",
column 42,group total of trxamt using "###,##&",
column 64,group total of trxprofit using "###,##&"
end
if trxtype = "E" and trxcode = "T" then
begin
print column 13,group count using "###,##&",
column 21,"Empenos Transferidos",
column 42,group total of trxamt using "###,##&",
column 64,group total of trxprofit using "###,##&"
end
if trxtype = "C" and trxcode = "F" then
begin
print column 13,group count using "###,##&",
column 21,"Compras Fundidos",
column 42,group total of trxamt using "###,##&",
column 64,group total of trxprofit using "###,##&"
end
if trxtype = "C" and trxcode = "T" then
begin
print column 13,group count using "###,##&",
column 21,"Compras Transferidos",
column 42,group total of trxamt using "###,##&",
column 64,group total of trxprofit using "###,##&"
end
if trxtype = "P" and trxcode = "R" then
begin
print column 13,group count using "###,##&",
column 21,"Plateria Retirados",
column 42,group total of trxamt using "###,##&",
column 64,group total of trxprofit using "###,##&"
end
if trxtype = "P" and trxcode = "F" then
begin
print column 13,group count using "###,##&",
column 21,"Plateria Fundidos",
column 42,group total of trxamt using "###,##&",
column 64,group total of trxprofit using "###,##&"
end
if trxtype = "E" and trxcode = "E" then
begin
print column 13,group count using "###,##&",
column 21,"Empenos Nuevos",
column 42,group total of trxamt using "###,##&",
column 62,group total of trxprofit using "-,---,--&"
end
if trxtype = "C" and trxcode = "C" then
begin
print column 13,group count using "###,##&",
column 21,"Compras Nuevas",
column 42,group total of trxamt using "###,##&",
column 62,group total of trxprofit using "-,---,--&"
end
on last row
print column 14,"======",
column 62,"========="
print column 13,count using "###,##&",
column 62,total of trxprofit using "-,---,--&"
end
生成简短的报告:
Merged transactions from old and new systems.
Totals from SEP-01-10 to SEP-30-10
COUNT TOTAL PROFIT
------ ------- --------
32 New Purchases 4,383 -4,383
73 New Pawns 12,875 -12,875
20 Purchases Sold 2,001 491
53 Forfeited Pawns 193 5,172
82 Interest Payments 1,602 1,602
47 Redeemed Pawns 8,457 1,059
====== =========
307 -8,934