Error compiling ACE report with multiple SELECT INTO statements?

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.

Up vote 0 down vote favorite share g+ share fb share tw.

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 = $sfecha and pwd_trx_date = $sfecha and pwd_trx_date = $sfecha and pwd_trx_date = $sfecha and pwd_trx_date = $sfecha and pwd_trx_date.

– Michael Petrotta Oct 4 '10 at 20:55 Odds on: -201 A syntax error has occurred. It might be slightly more meaningful (an ACE-specific error number) but only barely better than that. – Jonathan Leffler Oct 4 '10 at 21:19 @Michael- I'm getting: GRAM ERR -8018 LINE 34 CHAR 8 (construct undesireable...) + SYNTAX ERR -201 + GRAM ERR -8018 LINE 50 CHAR 8 (construct undesireable).. I updated the sample code with the error msgs where they ocurred.

– Frank Computer Oct 4 '10 at 21:28.

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 = $sfecha and pwd_trx_date 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.

OK, I'll try your answer. So then if I alias the table columns, do I need to specify then in the DEFINE VARIABLE section of ACE or does ISQL dynamically handle this when creating the temp table? – Frank Computer Oct 4 '10 at 21:39 @Frank: basically, ACE takes care of the issue.

Your temporary tables have the given column names - you can then join the temp table with other tables using the given column names. When the final query (the one with the ORDER BY, etc) runs, any columns from the temporary tables have the names assigned in the final SELECT or the names in the temporary tables; you can then use those names in the PRINT statements, etc, in the FORMAT section of the report. So, usually, you do not need anything extra in the DEFINE section of the report (in ACE).

(In I4GL, the rules are a little different. ) – Jonathan Leffler Oct 4 '10 at 21:48 @Jonathan: Ok, so I fixed code according to your suggestions, however compiler complained about each SELECT INTO not having each column in a GROUP BY so I added them, however now getting GRAM ERR on FORMAT statement.. This has become a real hairy report! – Frank Computer Oct 4 '10 at 22:51 @Frank: Oops; yes, forgot that you'd need to add the GROUP BY stuff - sorry for not noticing, but this is why you have compilers.

Your problem now is that your last query also selects into a temp table - that is a no-no; the last SELECT must select so the report can print its data. (And that - last SELECT may not be INTO TEMP - has not changed since the days of Informix 2.10, predating ISQL 2.10, let alone 2.10, 2.10, 2.10.03, 2.10 and 2.10! ) – Jonathan Leffler Oct 4 '10 at 22:54 @Jonathan: OK, so what is my last bonafide SELECT (with no INTO TEMP) statement supposed to look like?

Do I have to join with all the temp tables in order to print this trivial stuff?.. I have no idea where to begin! – Frank Computer Oct 5 '10 at 2:31.

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.

Are you sure?.. the doc samples and SQL ref man show/say the INTO TEMP clause goes right after the last table. Column in the SELECT statement. – Frank Computer Oct 4 '10 at 21:30 1 @Frank Computer: See this link which indicates that INTO TEMP should be the last clause included.

– Joe Stefanelli Oct 4 '10 at 21:39 OK, You and Jonathan are correct about the proper syntax. I used syntax used for embedded DECLARE statements. – Frank Computer Oct 4 '10 at 21:46.

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

The INTO clause after the SELECT list and before the FROM clause is appropriate for embedded SQL - ESQL/C, ESQL/COBOL - and not otherwise. Even then, the keyword TEMP would cause problems appearing there. – Jonathan Leffler Oct 4 '10 at 21:21 @Jonathan: wow, I used the format specified in an example of a DECLARE statement in the SQL Syntax ref man.. thought there was just one format used informix-wide when it came to SELECTing INTO... – Frank Computer Oct 4 '10 at 21:42 @Frank: there's a big difference between the INTO TEMP clause and the INTO clause for embedded SQL - as indicated in my prior comment.

You were unfortunate to look at the wrong part of the manual - I'm not sure whether there was an easy way to avoid such confusion (other than knowing the answer already). – Jonathan Leffler Oct 4 '10 at 21:46.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.