Error compiling ACE report with multiple SELECT INTO statements

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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;

Answer 4

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