当前位置： 文江博客话题详情

使用多个 SELECT INTO 语句编译 ACE 报告时出错

发布于 2024-09-26 05:43:48 字数 5521 浏览 4 评论 0原文

INFORMIX-SQL 4.10：

好的，因此在修复 INTO TEMP 语法并使用 AS 别名之后，ACE 编译器抱怨 GROUP BY 子句中没有每个别名，因此我将其添加到每个 SELECT 语句中。但是现在我仍然在 FORMAT 语句上收到 GRAM ERR（请参阅更新的代码示例）

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

INFORMIX-SQL 4.10:

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

新人笑 2024-10-03 05:43:48

INTO TEMP 子句是最后一个子句 - 必须位于 FROM、WHERE、GROUP BY 和 HAVING 子句之后。它不能与 ORDER BY 一起出现，但也会出现在 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;

您的问题“最终 SELECT 是什么样子”的答案取决于报告应该生成的内容。我复制了整个报告（253 行），应用了一些最小的重新格式化，并将其减少到 193 行（单行上的 GROUP BY 子句；使用 pwd_trx_date BETWEEN $sfecha AND $efecha日期范围）。

您有 6 个临时表，全部从同一个 Boletos 表中选择，具有相同的日期范围，并且在 pwd_trx_type 和 pwd_last_type 字段上具有不同的条件集。不幸的是，这 6 个查询展示了 3 组不同的分组列，以及许多不同数量的字段。

目前尚不清楚这些结果是否应与 UNION 或一组联接组合在一起。

如果答案是 UNION，则需要确保所有中间表具有与“r”（具有最多列的临时表）相同的列数，否则您必须使用提供的虚拟字段编写 UNION每个“窄”表都与“最宽”表相匹配。

如果答案是 JOIN，您将需要定义连接条件 - 我们这些不熟悉您的 DBMS 的人获得正确连接的机会为零。

无论答案是什么（JOIN 或 UNION），您还需要在 SELECT 中使用 ORDER BY 子句。这将控制数据呈现给报告的顺序。

您的“BEFORE GROUP OF”和“AFTER GROUP OF”子句一次只能列出一个变量。这些变量必须位于最终 SELECT 的 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 列。我认为您需要将查询构造为 UNION 查询。
我认为这意味着您将像这样修改您的主要查询系列：

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 值允许您正确打印标题。

然后您的 UNION 将能够执行以下操作：

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

您的 FORMAT 部分将包含：

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 的“真实”值 - 您可能只需选择 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

不过，从现在开始，你就得靠自己了。

The INTO TEMP clause is the last one - must go after FROM, WHERE, GROUP BY and HAVING clauses. It cannot appear with ORDER BY, but would come after that too.

Hence (using just the first query as an example):

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;

If, as seems plausible, you need more columns renamed, then:

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;

The answer to your question 'what does the final SELECT look like' depends on what the report is supposed produce. I've taken a copy of the entire report (253 lines), applied some minimal reformatting and reduced it to 193 lines (GROUP BY clause on a single line; use pwd_trx_date BETWEEN $sfecha AND $efecha for the date range).

You have 6 temporary tables, all selecting from the same Boletos table, with the same date range, and with different sets of criteria on the pwd_trx_type and pwd_last_type fields. Unfortunately, those 6 queries exhibit 3 different sets of grouping columns, and that many different numbers of fields.

It is not clear whether these results should be combined with a UNION or a set of joins.

If the answer is a UNION, you will need to ensure that all the intermediate tables have the same number of columns as 'r' (the temp table with the most columns), or you will have to write your UNION with dummy fields provided for each of the 'narrow' tables to match the 'widest' table.

If the answer is JOIN, you will need to define the joining conditions - those of us not familiar with your DBMS have zero chance of getting the joins right.

Whatever the answer (JOIN or UNION), you will also need an ORDER BY clause in the SELECT. This will control the order in which the data is presented to the report.

Your 'BEFORE GROUP OF' and 'AFTER GROUP OF' clauses can only list one variable at a time. Those variables will have to be in the ORDER BY clause of the final SELECT.

It looks a bit as if you want to concatenate 6 separate reports: one report using the temp table r; the next using the temp table 'i', then 'f', etc. This is not a structure that lends itself to easy ACE reporting. It might be best to have 6 separate reports, each written to a separate file, and then combine (concatenate) those separate reports. You'd probably arrange for the dates to be provided as arguments rather than inputs, so the user is only obliged to enter the dates once, but the controlling shell script runs the 6 reports with the same two dates as parameters.

Given the temp table 'r' shown above, the output formatting associated with that (in your mind - unfortunately, ACE has a different view on things) is:

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

Most of the other formatting sections are somewhat similar - they have the same four columns. Two sections have just 3 columns. I think you are going to want to structure your query as a UNION query.
I think this means that you will be revising your main series of queries like this:

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;

Your other queries will produce the same number of columns. The rsequence ensures that the rows from 'r' will appear before the rows from 'i' (which will have rsequence = 2) and 'f' (which will have rsequence = 3) and so on. The rlabel values allow you to print the heading correctly.

Your UNION will then be able to do:

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

Your FORMAT section will then contain:

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

Since there are two groups where you don't have a 'real' value for rcol4 - you will probably simply select 0 or SUM(0) as a dummy column for them, you might instead need:

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

From here on, you are on your own, though.

回复 原文
雪若未夕 2024-10-03 05:43:48

自从我使用 Informix 以来已经有一段时间了，但我认为 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;

Been a while since I did any Informix, but I thought the INTO TEMP clause came at the end of the query and I'm not sure about specifying the column names as part of the temp table. Try this version instead:

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;
回复 原文
深居我梦 2024-10-03 05:43:48

仅当选择返回恰好一行时，语法“SELECT ... INTO”才有效。

如果不完全有一个结果，您将收到类似“子请求返回的结果不完全是一行”的错误。

The syntax "SELECT ... INTO" works only if the select returns exactly one row.

If there is not exactly one result, you will get an error like "A sub-request returned not exactly one row".

回复 原文
唱一曲作罢 2024-10-03 05:43:48

好吧，我通过使用 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

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

Producing the dseried report:

                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
回复 原文
~没有更多了~

关于作者

花海

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

1CH1MKgiKxn9p

文章 0 评论 0

ゞ记忆︶ㄣ

文章 0 评论 0

JackDx

文章 0 评论 0

信远

文章 0 评论 0

yaoduoduo1995

文章 0 评论 0

霞映澄塘

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文