Classics Forum

Re: Multiple columns of output on ACE

Posted By:
Date:

Yes, but it isn't all that easy. This is an example from my (long out of
print) book "Using Informix-SQL":


{
@(#)addrlist.ace 5.3 91/02/26
Address label generating report
}

DATABASE Cars END

DEFINE
{ Restrict data selected by report }
PARAM[1] early DATE
PARAM[2] late DATE
VARIABLE matchstr CHAR(30)
VARIABLE col1 INTEGER { Column for 1st label }
VARIABLE col2 INTEGER { Column for 2nd label }
VARIABLE col3 INTEGER { Column for 3rd label }
VARIABLE aname CHAR(28) { Storage for 1st label }
VARIABLE aline1 CHAR(28) VARIABLE aline2 CHAR(28)
VARIABLE aline3 CHAR(28) VARIABLE aline4 CHAR(28)
VARIABLE bname CHAR(28) { Storage for 2nd label }
VARIABLE bline1 CHAR(28) VARIABLE bline2 CHAR(28)
VARIABLE bline3 CHAR(28) VARIABLE bline4 CHAR(28)
VARIABLE cname CHAR(28) { Storage for 3rd label }
VARIABLE cline1 CHAR(28) VARIABLE cline2 CHAR(28)
VARIABLE cline3 CHAR(28) VARIABLE cline4 CHAR(28)
VARIABLE n_addr INTEGER { No. of stored addresses }
VARIABLE xname CHAR(28) { Used to build label }
VARIABLE xline1 CHAR(28) VARIABLE xline2 CHAR(28)
VARIABLE xline3 CHAR(28) VARIABLE xline4 CHAR(28)
END

INPUT
PROMPT FOR matchstr USING "Enter pattern for surnames: "
END

OUTPUT
REPORT TO PRINTER
PAGE LENGTH 64
TOP MARGIN 3
BOTTOM MARGIN 1
LEFT MARGIN 0
END

SELECT Salut, Firstname, Surname,
Address1, Address2, Address3, Postcode
FROM Customers
WHERE Address1 IS NOT NULL
AND (Address2 IS NOT NULL OR Address3 IS NOT NULL)
AND (Salut IS NOT NULL OR Firstname IS NOT NULL)
AND Contactdate BETWEEN $early AND $late
AND Surname MATCHES $matchstr
ORDER BY Surname, Firstname
END

FORMAT

FIRST PAGE HEADER
LET col1 = 13
LET col2 = 44
LET col3 = 75
LET n_addr = 0
LET aname = "" LET bname = "" LET cname = ""
LET aline1 = "" LET aline2 = "" LET aline3 = "" LET aline4 = ""
LET bline1 = "" LET bline2 = "" LET bline3 = "" LET bline4 = ""
LET cline1 = "" LET cline2 = "" LET cline3 = "" LET cline4 = ""

ON EVERY ROW
{ Sort out label into x-variables }
IF salut IS NOT NULL THEN { Sort out name }
LET xname = salut CLIPPED, " ", firstname[1]
ELSE
LET xname = firstname[1]
LET xname = xname CLIPPED, " ", surname CLIPPED
LET xline1 = address1
IF address2 IS NULL THEN BEGIN
LET xline2 = address3
LET xline3 = postcode
END ELSE BEGIN
LET xline2 = address2
IF address3 IS NULL THEN BEGIN
LET xline3 = postcode
END ELSE BEGIN
LET xline3 = address3
LET xline4 = postcode
END
END
IF n_addr = 0 THEN BEGIN { Store 1st address }
LET aname = xname
LET aline1 = xline1
LET aline2 = xline2
LET aline3 = xline3
LET aline4 = xline4
END
IF n_addr = 1 THEN BEGIN { Store 2nd address }
LET bname = xname
LET bline1 = xline1
LET bline2 = xline2
LET bline3 = xline3
LET bline4 = xline4
END
IF n_addr = 2 THEN BEGIN { Store 3rd address }
LET cname = xname
LET cline1 = xline1
LET cline2 = xline2
LET cline3 = xline3
LET cline4 = xline4
END
LET n_addr = n_addr + 1
IF n_addr = 3 THEN BEGIN { Print data if required }
SKIP 2 LINES
PRINT COLUMN col1, aname, COLUMN col2, bname, COLUMN col3,
cname
PRINT COLUMN col1, aline1, COLUMN col2, bline1, COLUMN col3,
cline1
PRINT COLUMN col1, aline2, COLUMN col2, bline2, COLUMN col3,
cline2
PRINT COLUMN col1, aline3, COLUMN col2, bline3, COLUMN col3,
cline3
PRINT COLUMN col1, aline4, COLUMN col2, bline4, COLUMN col3,
cline4
SKIP 3 LINES
LET n_addr = 0
LET aname = "" LET bname = "" LET cname = ""
LET aline1 = "" LET aline2 = "" LET aline3 = "" LET aline4 = ""
LET bline1 = "" LET bline2 = "" LET bline3 = "" LET bline4 = ""
LET cline1 = "" LET cline2 = "" LET cline3 = "" LET cline4 = ""
END

ON LAST ROW
{ Flush outstanding addresses }
IF n_addr > 0 THEN BEGIN
SKIP 2 LINES
PRINT COLUMN col1, aname, COLUMN col2, bname
PRINT COLUMN col1, aline1, COLUMN col2, bline1
PRINT COLUMN col1, aline2, COLUMN col2, bline2
PRINT COLUMN col1, aline3, COLUMN col2, bline3
PRINT COLUMN col1, aline4, COLUMN col2, bline4
SKIP 3 LINES
END

END


The schema for the table is designed for use in the UK, so you may want to
adapt it a bit:

{
@(#)customer.sql 5.2 91/03/22
Database Cars: Table Customers
}

CREATE TABLE Customers
(
Number SERIAL(1000) NOT NULL,
Salut CHAR(5),
Firstname CHAR(10),
Surname CHAR(15) NOT NULL,
Address1 CHAR(24),
Address2 CHAR(24),
Address3 CHAR(24),
Postcode CHAR(8),
Phone CHAR(16),
Maxmoney MONEY(6,0),
Enquiry CHAR(1) NOT NULL,
Lastmodel INTEGER,
Contactdate DATE
);
{
Number - Customer number
Salut - Salutation
Firstname - Personal name
Surname - Family name
Address1 - 1st line of address
Address2 - 2nd line of address
Address3 - 3rd line of address
Postcode - Postcode (UK)
Phone - Telephone number
Maxmoney - Estimated spending power
Enquiry - Type of enquiry
Lastmodel - Model enquired about
Contactdate - Date contacted
}
{
PRIMARY KEY Customers(Number)
FOREIGN KEY Customers(Lastmodel)
REFERENCES Models(Model)
}
CREATE UNIQUE INDEX Pk_customers ON Customers(Number);
CREATE INDEX F1_customers ON Customers(Lastmodel);
CREATE INDEX I1_customers ON Customers(Surname);

Note that this schema pre-dates the general availability of PRIMARY KEY and
FOREIGN KEY constraints - so they are commented rather than in use. And
here is some sample data:

1001|Mr|Colin|Askestrand|6 Warwick Court|MADRID - 10|||419
8051|7500.0|S|92|24/01/1984|
1002|Mr|R.|Bycroft|Simpson Parkway|Yelvertoft|NORTHAMPTON|NN6
7LF||7500.0|S|21|27/09/1983|
1003|Mr|P.|Tinkler|University of East Angli|RAC House, Landsdowne
Rd|CROYDON|CR9 2JA||7500.0|S|85|24/08/1983|
1005|Mr|J.|Goodman|754 Roadhampstead St|Fleetville|ST ALBANS,
Herts|||7500.0|S|38|23/07/1984|
1006|Mr|T.A.|Janetta|Room 2036, Tenter House|375 Kensington High
St|LONDON|W14 8QX||7500.0|S|109|18/12/1984|
1009|Mr|Richard|Plumb|14-15 Lemon Street|Paisley|RENFREWSHIRE|PA1
2BE|0418871241 x201|7500.0|S|18|05/09/1983|
1010|Mr|B.|Jevons|Neville House|Galway|IRELAND||091
64730|7500.0|S|104|21/06/1983|
1012|Mr|E|Hewett|21-24 Bury St|Martlesham Heath|Ipswich, Suffolk|IP5
7RE|Over 4999|7500.0|S|19|24/09/1983|
1013|Mr|G.W.|Randal|Shropshire House|IBM UK IPL|42 The Centre,
Feltham|Middlx||7500.0|S|47|10/06/1983|
1015|Mr|Peter|Jacobson|C.I.S. House|LONDON||W18 OAN|01 493
8040|7500.0|S|35|06/07/1984|
1018|Mr|G.|Brys|13 Oakenshaw Close|Bogerud 0621|OSLO 6,
Norwa|||7500.0|S|148|26/06/1984|
1019|Mr|M.J.R.|Hunter|PO Box 55|IPSWICH|SUFFOLK|IP3 3JW|0473
219661|7500.0|S|62|24/10/1983|
1021|Mr|S.E.|Haug|Bankcentre|SLIGO|Ireland||071
3261|7500.0|S|48|12/04/1984|
1023|Mr|W.|Merritt|Avening Green|LONDON||W1|01 636
1023|7500.0|S|123|12/11/1983|
1024|Dr|G.|Twyman|The Broadway|||EC2A 2DU||7500.0|S|129|10/01/1984|

--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management Solutions
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"


|---------+---------------------------->
| | "Jay Zwagerman" |
| | <jay2@iafalls.com|
| | > |
| | Sent by: |
| | forum.subscriber@|
| | iiug.org |
| | |
| | |
| | 02/28/2003 06:35 |
| | AM |
| | |
|---------+---------------------------->
>---------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: classics@iiug.org |
| cc: |
| Subject: Multiple columns of output on ACE [108] |
| |
>---------------------------------------------------------------------------------------------------------------------------------------------|




Hello again

I have another question about the ACE report. I currently have a report
that prints out names and addresses on every row in the report. So the
output is like this:

Name
Address1
Address2
City, State Zip

Name
Address1
Address2
City, State Zip

and so on. I was wondering if it is possible to create this output in
multiple columns like this

Name Name Name
Address1 Address1 Address1
Address2 Address2 Address2
City, State Zip City, State Zip City, State Zip


Name "" ""
Address1
Address2
City, State Zip

Messages In This Thread

• Multiple columns of output on ACE
  Jay Zwagerman -- Tuesday, 15 April 2003, at 3:55 p.m.
  • RE: Multiple columns of output on ACE
    Richard E. Ghiz -- Tuesday, 15 April 2003, at 3:55 p.m.
  • Re: Multiple columns of output on ACE
    Joe Vigil -- Tuesday, 15 April 2003, at 3:55 p.m.
    • RE: Multiple columns of output on ACE
      Miguel Caldera -- Tuesday, 15 April 2003, at 3:55 p.m.
      • RE: Multiple columns of output on ACE
        Richard E. Ghiz -- Tuesday, 15 April 2003, at 3:55 p.m.
      • Re: Multiple columns of output on ACE
        Joe Vigil -- Tuesday, 15 April 2003, at 3:55 p.m.
  • Re: Multiple columns of output on ACE
    Jonathan Leffler -- Tuesday, 15 April 2003, at 3:55 p.m.

Classics Forum is maintained by Administrator with WebBBS 5.12.