Insert Into statement comparable to a CPYF command

[Guest.Visitor]

Group,
I have two files -
file#1 is member of a PF (FILE A) keyed by donor#, (an alias has been created to point to the specified member)
file# 2 is a SQL TABLE (FILE B) - no key order.
FILE B is a temporary work table, after FILE B is built with the data, the following statement runs: insert into mylib/FILEA (select * from mmalib/FILEB). When the statement runs, I need it to copy the data from FILEB into FILEA, leaving it in the order of FILEB, instead when the script runs, FILE A has the data in donor# order, not sequence order as seen in FILE B. I'm looking for a comparable statement such as the COPYF command that allows me to copy the data into a keyed file by record#1 to end, thus overriding the keyed order.

Any suggestions or links would be helpful.

[efnkay]

DMM...I don't think you can get there from here. I mean...No matter what sequence you copy or insert the data into FILE A, the order of the records inserted are going to be in "key" order. No doubt if your FILE A was not keyed, your records would be inserted at the end of the file assuming you weren't re-using deleted records on the PF. However if you had a logical over the PF or you just retrieve records from the PF and order by RRN, you might get close to what you need. Good Luck!

[Guest.Visitor]

efnkay -
Your statement - "No matter what sequence you copy or insert the data into FILE A, the order of the records inserted are going to be in "key" order."

If I CPYF from record 14 to 300 into FILEA, my records copy into the exactly that way, it isn't ordered by the "key" which is donor#, although if I use the command CPYF with options from START to END - then my FILEA is in "key" order. For this project, I have to have it in rrn order. Since my FILEB is a DDM file, I can't use the CPYF command which would do exactly what I need, instead I am using the INSERT statement, which defaults to the "key" order when inserting.

With my statement,
"insert into mylib/FILEA (select * from mylib/FILEB)", I would like to add:
order by mylib/FILEB - but that isn't a choice.

Thanks for the post.

[efnkay]

DMM...I'm deaf in one eye, and I can't see anything out of my other ear. I guess you mean when you DSPPFM on FILEA after your copy of 14 to 300, they are in the order you copied...yes? But (SELECT * FROM FILEA) of course shows you the records in PF's key order. (donor#)

You can return the RRN of the records in either file. (SELECT RRN(B.*), B.* FROM FILEB B ORDER BY 1). Hope this helps. Good luck!