I’m using Postgres 9.3 on Windows. I am attempting to insert several rows into a table using data from another table. It is inserting correctly, but the order isn’t correct. Is an ‘order by’ command not recognized in this situation?
Here are the two tables:
CREATE TABLE accounts (
acctno char(22) PRIMARY KEY,
acctdesc varchar(60),
...
accttype integer
);
CREATE TABLE rprtfrmt (
lineno integer PRIMAY KEY,
bdgtacct char(22),
prntline integer,
addline integer,
totllevl integer,
desconly integer,
prntundrln integer,
balshtentry integer,
rprttype integer,
blnkline integer
);
Here are the commands I’m using:
CREATE TEMP SEQUENCE rprtfrmt_seq INCREMENT BY 50 START WITH 50;
INSERT INTO rprtfrmt (lineno, bdgtacct, prntline, addline, totllevl, desconly, prntunderln, balshtentry, rprttype, blnkline)
(SELECT nextval(‘rprtfrmt_seq’), acctno, 1,1,0,0,0,0,0,1 FROM accounts WHERE (accttype = 0 OR accttype = 2) ORDER BY acctno)
It inserts all of the line numbers, account numbers, and ‘fixed’ data into the rprtfrmt table, but not in account number order. Any help will be greatly appreciated.
Thanks,
Lynn