Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004

    Question Unanswered: Report formatting staying in buffer

    I am trying to create 5 files that create tables, insert values in the table, and list the contents of the table in a simple report. The problem I am running into is that the report formatting seems to be staying in the buffer so that when I load the next file, the first thing that happens is that the report is run and it maintains that formatting.
    Here is the example of one of my files. Any help would be appreciated.

    /*SHPMCF FOR CPT 221 This will create the Master Customer File (MCF)*/
    --AND List the contents of the File in a simple report

    create table shpmcf(
    MCF_custnum char(7) primary key,
    MCF_custname char(25),
    MCF_attn char(25),
    MCF_street char(25),
    MCF_city char(15),
    MCF_state char(2),
    MCF_zip char(5),
    MCF_creditrate char(1),
    MCF_creditlimit numeric(7,2),
    MCF_balance numeric(8,2),
    MCF_YTDSales numeric(8,2),
    MCF_YPayments numeric(8,2));

    desc shpmcf;

    insert into shpmcf values(1000001, 'Big Bear Computers','Jones,Rita', '32 main street', 'COLUMBUS', 'OH', '43220','A', 15000.00, 101000.00,500000.00,444444.04);
    insert into shpmcf values(1000002, 'Kroger Hard Drives','Smith,John','Aviator avenue', 'PITTSBURGH', 'PA', '14228','A',15000.00, 101000.50,400000.00,555555.05);
    insert into shpmcf values(1000003, 'Wal-Mart Modems','Frank,Michael', '33 Grove Blvd', 'ORLANDO', 'FL', '32915', 'B', 13000.00, 303000.30,300000.00,666666.66);
    insert into shpmcf values(1000000, 'Big Ten Stores','Sabin,Nick', '30 Grove Blvd', 'ORLANDO', 'FL','32915', 'C',12000.00, 430000.00,300000.00, 333333.33);
    insert into shpmcf values(1000004, 'Rat-Pack Mart','Davis,Sammy', '1313 Sawmill Rd', 'COLUMBUS','OH', '43212', 'C',12000.00, 555555.55, 444444.44, 111111.11);
    insert into shpmcf values(1000005, 'Left Alone Market','Garbo,Greta', '55 Innis St', 'ATLANTA', 'GA', '33983', 'A',15000.00, 101000.75, 400000.00, 222222.22);
    insert into shpmcf values(1000006, 'Shoot-Em Up Store','James,Jesse', '43 Union St', 'Wheeling', 'OH', '46602', 'A', 15000.00, 250000.00,495000.44, 123456.78);
    insert into shpmcf values(1000007, 'Seen My Friend, Inc','Martin,Abraham', '44 Seven Gables', 'PALM BAY', 'FL', '32901', 'B', 13000.00,101000.01,555555.55,987654.13);
    insert into shpmcf values(1000008, 'That Is Great, Inc!','Firestone,Roy', '14 Roberts Rd', 'COLUMBUS', 'OH', '44226', 'B',13000.00, 150000.00, 666666.66, 555555.44);
    insert into shpmcf values(1000009, 'Dynasty Cosmetics','Evans,Linda', '8713 Etna', 'PITTSBURGH', 'PA', '14208', 'C', 12000.00, 120000.00, 600000.01, 444444.43);
    insert into shpmcf values(1000100, 'Rainbow Market','Jackson,Jesse', '4820 Paris St', 'LAMAR', 'TX', '21222', 'D', 11000.00, 150000.00, 756321.12, 777777.78);
    insert into shpmcf values(1000101, 'Bull Durham and Sons','Sarandon,Susan', '44 Star Bld', 'MORGANTOWN', 'WV', '34228', 'D', 11000.00, 220000.00,897456.12, 888888.87);
    insert into shpmcf values(1000102, 'ESPN The Store','Patrick,Robert', '1900 Clarsburg Ave', 'COLUMBUS', 'OH', '41828', 'F', 10000.00, 500000.50, 666664.12, 999123.33);
    insert into shpmcf values(1000103, 'INXS Grocery','Pumpkineater,Peter', '7512 15th St', 'PITTSBURGH', 'PA', '44998','A', 15000.00, 150000.50,456123.12, 544544.54);
    insert into shpmcf values(1000104, 'Has Beens R Us','Douglus,Buster', '33 Beechurst', 'MORGANTOWN', 'WV', '32101', 'A', 15000.00, 250000.00, 987654.32, 888888.78);

    Pause 'LIST THE FILE';
    select * FROM SHPMCF;

    ----Create a report listing the file

    clear columns;
    clear breaks;
    ttitle off;
    btitle off;
    set pagesize 60;
    set linesize 110;
    ttitle 'Master Customer File|Report Listing';
    btitle 'End of Master Customer File Listing Page 'sql.pno;
    column MCF_custnum heading 'Customer|Number';
    column MCF_custname heading 'Customer|Name';
    column MCF_custname format a12;
    column MCF_custname truncated;
    column MCF_attn heading 'Client|Contact';
    column MCF_creditlimit format $999,999.99;
    column MCF_balance format $9,999,999.99;
    column MCF_YPayments format $9,999,999,99;
    column MCF_creditlimit heading 'Credit|Limit';
    column MCF_ytdsales heading 'Customer Sales' format $999,999.99;

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    Well, I'd put SELECT statement at the end of the file, not between INSERT statements and the formatting part.

    If this is your typical file, SELECT uses formats that are left from previous file.

    So, I suggest something like this:
    PHP Code:
    DESC ...

    CLEAR ...
    TTITLE ...
    COLUMN ... FORMAT ...

    SELECT FROM ... 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts