newb question about sql+ script for appending to end of table

[JWM81274]

Is there a statement that will allow appending to the end of table when inserting new data? I use multiple "insert into table1(timestam p1,area1,value1 ,value2...)" to insert different area data with the same timestamp into the same table. When I look at the data in the table sometimes the rows are placed randomly into the table. When I queue a php script to display the data for a particular day the order changes because the order in the table changes. I know there are ways to correct this when pulling the data from the oracle table but I am no PHP expert. This code was written by someone else and for me to change it may cause more harm than good. I can add a column and a qualifier (areanumber) to order them that way in the php file but thought if there was a way to add the data to the bottom which would keep them in the same order, I hope, or even some way of keeping the inserted rows order the same it would solve my issue. I hope this is clear enough. Thanks for your help.

[amitpatel66]

You can use ORDER BY ROWID to order the data in ascending order in which the data is inserted in the table.

[JWM81274]

You can use ORDER BY ROWID to order the data in ascending order in which the data is inserted in the table.

It appears as though it is the ROWID that is out of order. I looked at the ROWIDs and for some reason the first insert statement is getting an ID that is not in order with the rest. Sometimes the ID is an earlier one sometimes it is a later one. Here is the script I am using, shortened for display:

INSERT INTO "TABLE1" (DATE1, SHOPS, KILN_DAY, DAY_OF_YEAR)
VALUES (LOCAL.DATE1, 'COAL',"HISTORIAN DATA",LOCAL.DAYOFTHEYEAR);

INSERT INTO "TABLE1" (DATE1, SHOPS, KILN_DAY, DAY_OF_YEAR)
VALUES (LOCAL.DATE1, 'COAL BTU',"HISTORIAN DATA",LOCAL.DAYOFTHEYEAR);

INSERT INTO "TABLE1" (DATE1, SHOPS, KILN_DAY, DAY_OF_YEAR)
VALUES (LOCAL.DATE1, 'COAL BTU/TON',"HISTORIAN DATA",LOCAL.DAYOFTHEYEAR);

This code is written in SQL+ but it is a version used by the historian "Aspen IP21" not Oracle. We were unable to get the link set up so I am pushing the data to Oracle rather than pulling it. The Historian data is a select statment to pull unique data for that shop. Hope this sheds more light on my issue.

Thanks again for the help.

[asp2]

according to my search results ,

if the SELECT statement does not have a specific ORDER BY , then there's no way you can determine exactly in which order data will come .


try it yourself on any table and select from that table once witout an order by , and else with order by rowid , and you will see yourslelf ..

the best way is to add an order by in the program you are using

you can at least order by ROWID

[JWM81274]

I just added a column to all the tables that I placed an numeric value and ordered by that column. ROWID didn't work because the order created is not the same as when you order by ROWID. The ID created uses + ,\ , "A", "a", and #'s 0-9 in the ID that order. When you order by ROWID ASC the + is at the top then the \, followed by 0 through 9, then "A" and lastly "a". If the ROWID of the values crosses from letters to numbers the order will be wrong.

[amitpatel66]

Are you looking at orderding the data based on the ASCII value of each rows data for a particular column?..When you use ROWID, it will order the records based on when the record is inserted in the table. in ASC order, records inserted latest will be listed at last.

[JWM81274]

I am trying to order by inserted in the table. ROWID ASC(ascending order) doesn't match when it was inserted. I tested with inserting 9 rows at one timestamp 10 times and when sorted by ROWID ASC(ascending order) the timestamp rows don't always allign. I don't understand why exactly. It maybe the ASPEN IP21 version of SQL+.

I did fix my issue another way though.

[JWM81274]

Thanks for all the help.

[amitpatel66]

Good to hear that your issue is resolved. It would be great if you can post the solution of how did you solve your problem so that in case any other user faces similar issue they can refer to this thread for help.

[JWM81274]

To fix my issue I added another column called "order1" to all the oracle tables I am using. When the data was pushed into oracle I also pushed the order I wanted displayed on the PHP page into the column "order1". In the script for PHP I used order by "order1" asc. Which ordered the data correctly. Thanks again for the help.