代码之家 › 专栏 › 技术社区 › Joe R.

使用多个select into语句编译ace报告时出错

informix sql

Joe R. · 技术社区 · 14 年前

Informix-SQL 4.10:

好吧,在修正了in to-temp语法并将其用作别名之后,ace编译器抱怨在group-by子句中没有每个别名,所以我将其添加到了每个select语句中。但是,现在我仍然在FORMAT语句中得到一个GRAM错误(请参阅更新的代码示例)

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

4 回复 | 直到 14 年前

Jonathan Leffler 14 年前

into temp子句是最后一个-必须在from、where、group by和having子句之后。它不能与order by一起出现,但之后也会出现。

因此(仅以第一个查询为例):

select count(*) AS counter,  -- Aggregates or expressions must be named
       pwd_trx_date,
       pwd_trx_type,
       pwd_last_type,
       pwd_last_amt,
       pwd_pawn_amt,
       pwd_cob1,
       pwd_cob2,
       pwd_cob3,
       pwd_cob4
  from boletos
 where pwd_trx_date >= $sfecha
   and pwd_trx_date <= $efecha
   and pwd_trx_type = "E"
   and pwd_last_type = "R"
  INTO TEMP r;

如有可能,您需要重命名更多的列,则:

select count(*)       AS rcount,
       pwd_trx_date   AS rtrxdate,
       pwd_trx_type   AS trxtype,
       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"
  INTO TEMP r;

您的问题“最终选择是什么样子”的答案取决于报告应该产生什么。我已经复制了整个报告(253行),应用了一些最小的重新格式化,并将其减少到193行(在一行上按子句分组;使用 pwd_trx_date BETWEEN $sfecha AND $efecha 用于日期范围)。

您有6个临时表,它们都是从同一个Boletos表中选择的,具有相同的日期范围,并且在 pwd_trx_type 和 pwd_last_type 领域。不幸的是,这6个查询显示了3组不同的分组列,以及许多不同数量的字段。

尚不清楚这些结果应该与联合还是一组联接组合在一起。

如果答案是联合,则需要确保所有中间表的列数与“r”(列数最多的临时表)相同,或者必须使用为每个“窄”表提供的虚拟字段编写联合,以匹配“最宽”表。

如果答案是join,您将需要定义连接条件——我们中不熟悉您的DBMS的人获得正确连接的机会为零。

无论答案是什么(join还是union),您都需要在select中使用order by子句。这将控制向报告显示数据的顺序。

“before group of”和“after group of”子句一次只能列出一个变量。这些变量必须在最终选择的ORDER BY子句中。

看起来有点像要连接6个独立的报表:一个报表使用临时表R;另一个报表使用临时表“i”,然后是“f”等。这不是一个可以轻松进行ACE报表的结构。最好是有6个单独的报告,每个报告都写在一个单独的文件中,然后合并(连接)这些单独的报告。您可能会安排日期作为参数而不是输入,因此用户只需要输入一次日期,但是控制shell脚本运行6个报告,其中两个日期作为参数。

鉴于上面显示的临时表“r”,与之相关的输出格式(在您的头脑中-不幸的是,ace对事物有不同的看法)是:

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 "###,###"

大多数其他格式化部分都有点相似——它们有相同的四列。两个部分只有3列。我认为您将希望将查询结构为联合查询。我认为这意味着你将修改你的主要系列查询,如下所示:

SELECT COUNT(*)           AS rcount,
       pwd_trx_date       AS rtrxdate,
       pwd_trx_type       AS rtrxtype,
       pwd_last_type      AS rlasttype,
       "RETIROS"          AS rlabel,
       1                  AS rsequence,
       SUM(pwd_last_amt)  AS rcol3,
       (SUM(pwd_cob1) + SUM(pwd_cob2) + SUM(pwd_cob3) + SUM(pwd_cob4) -
        SUM(pwd_pawn_amt) AS rcol4
  FROM boletos
 WHERE pwd_trx_date BETWEEN $sfecha AND $efecha
   AND pwd_trx_type = "E"
   AND pwd_last_type = "R"
 GROUP BY rtrxdate, rtrxtype, rlasttype
  INTO TEMP r;

您的其他查询将产生相同数量的列。rsequence确保“r”中的行出现在“i”(将rsequence=2)和“f”(将rsequence=3)中的行之前,依此类推。rlabel值允许您正确打印标题。

你的工会将能够做到:

SELECT * FROM r
UNION
SELECT * FROM i
UNION
...
SELECT * FROM c
ORDER BY rsequence, rtrxdate, rtrxtype, rlasttype

您的格式部分将包含:

PAGE HEADER
    PRINT COLUMN  1,"CANTIDAD INGRESOS    TOTAL              GANANCIA"
    PRINT COLUMN  1,"-------- --------- -------              --------"

BEFORE GROUP OF rsequence
    SKIP 1 LINE

ON EVERY ROW
    PRINT COLUMN  2, rcount USING "###,###",
          COLUMN 10, rlabel,
          COLUMN 20, rcol3,
          COLUMN 42, rcol4

由于有两个组没有rcol4的“real”值-您可能只需选择0或sum(0)作为它们的虚拟列,您可能需要:

ON EVERY ROW
    IF rsequence <= 4 THEN
        PRINT COLUMN  2, rcount USING "###,###",
              COLUMN 10, rlabel,
              COLUMN 20, rcol3,
              COLUMN 42, rcol4
    ELSE
        PRINT COLUMN  2, rcount USING "###,###",
              COLUMN 10, rlabel,
              COLUMN 20, rcol3

不过,从现在起,你只能靠自己了。

Joe Stefanelli 14 年前

我做了一段时间的信息混合,但我认为 INTO TEMP 子句出现在查询的末尾,我不确定是否将列名称指定为临时表的一部分。请尝试此版本:

select count(*) as rcount,
       pwd_trx_date,
       pwd_trx_type,
       pwd_last_type,
       pwd_last_amt,
       pwd_pawn_amt,
       pwd_cob1,
       pwd_cob2,
       pwd_cob3,
       pwd_cob4
  from boletos
 where pwd_trx_date >= $sfecha
   and pwd_trx_date <= $efecha
   and pwd_trx_type = "E"
   and pwd_last_type = "R"
  into temp r;

Benoit Courtine 14 年前

语法“select…进入“仅当select返回时有效” 恰好一行 .

如果没有一个结果,您将得到一个错误,如“返回的子请求不是一行”。

Joe R. 14 年前

我用order by和group by解决了这个问题。请注意,在SELECT语句的ORDER BY子句中,列必须以相反的顺序放置。以下ACE报告完成了挑战:

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