Thanks Andrej.
The problem seems to be that inside the 'insert' command, the 'select'
command isn't returning the accounts 'acctno' in 'acctno' order, even though
I'm including an 'order by acctno' option. The way the insert command is
written, I'm expecting the 'lineno' and 'bdgtacct' to both end up in
ascending order. If I query rprtfrmt and request 'order by lineno', the
'bdgtacct's should also be in order, but they aren't. (I hope this makes
sense.)
Thanks,
Lynn
-----Original Message-----
From: Andrej
Sent: Monday, June 02, 2014 6:00 PM
To: lmanorders
Cc: pgsql-novice
Subject: Re: [NOVICE] INSERT INTO FROM SELECT
Hi Lynn,
To the best of my knowledge the SQL standard doesn't define that data
inside a table needs
to be stored in any given order. If you expect to see it in a certain
sequence define that at query time,
when you retrieve the data?
Cheers,
Andrej
On 3 June 2014 11:52, lmanorders <lmanorders@gmail.com> wrote:
> 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
>
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes
concise.
http://www.georgedillon.com/web/html_email_is_evil.shtml