richardj@uhccux.uhcc.hawaii.edu (Richard Jablonka) (02/11/90)
I want to SORT an informix table. I only need to do it once. The problem is
making the names come up in the right order in an entry form. Unfortunately
in an entry form you can only state the table name and you can't use the where
or order by clause.
Additionally, I tried to set up a view using the order by clause but it just
says error when I tried.
I even tried "Select order by TO [newtable name]" but the program says
you cant use the order by clause when you "pipe" to a new table. Does anybody
have any suggestions?
Thanks,
Richard Jablonka, richardj@uhccuxgreg@halexii.uucp (Gregory F. Hogg) (02/11/90)
In article <6548@uhccux.uhcc.hawaii.edu> richardj@uhccux.UUCP (Richard Jablonka) writes: > >I want to SORT an informix table. I only need to do it once. The problem is >making the names come up in the right order in an entry form. Unfortunately As fas as I know there is no way todo what you want in INFORMIX-SQL. The best I have been able to do is to unload the table ordered the way you want and then reload it. This way the records are in the dbtable allready ordered. -- Greg Hogg COMPANY : HOGG'S SOFTWARE
sullivan@aqdata.uucp (Michael T. Sullivan) (02/13/90)
From article <6548@uhccux.uhcc.hawaii.edu>, by richardj@uhccux.uhcc.hawaii.edu (Richard Jablonka): > > I want to SORT an informix table. I only need to do it once. The problem is > making the names come up in the right order in an entry form. Unfortunately > in an entry form you can only state the table name and you can't use the where > or order by clause. My guess is that you index the order you want and make that a clustering index. Since perform appears to show rows in their raw table order, this will probably do what you want. -- Michael Sullivan uunet!jarthur!aqdata!sullivan aQdata, Inc. sullivan@aqdata.uucp San Dimas, CA +1 714 599 9992
cortesi@infmx.UUCP (David Cortesi) (02/13/90)
In article <1990Feb11.084522.12120@halexii.uucp> greg@halexii.UUCP (Gregory F. Hogg) writes: >In article <6548@uhccux.uhcc.hawaii.edu> richardj@uhccux.UUCP (Richard Jablonka) writes: >>I want to SORT an informix table. I only need to do it once. The problem is >>making the names come up in the right order in an entry form. Unfortunately > As fas as I know there is no way todo what you want in >INFORMIX-SQL. The best I have been able to do is to unload the table >ordered the way you want and then reload it. This way the records are >in the dbtable allready ordered. Loading a table in the desired order is the most efficient way, but to create an ordering or a new ordering after the fact you use the command CREATE CLUSTER INDEX. The command ALTER INDEX is used to refresh the ordering after inserts or updates.
clh@tacitus.tfic.bc.ca (Chris Hermansen) (02/13/90)
In article <1990Feb11.084522.12120@halexii.uucp> greg@halexii.UUCP (Gregory F. Hogg) writes: >In article <6548@uhccux.uhcc.hawaii.edu> richardj@uhccux.UUCP (Richard Jablonka) writes: >> >>I want to SORT an informix table. I only need to do it once. The problem is >>making the names come up in the right order in an entry form. Unfortunately > > As fas as I know there is no way todo what you want in >INFORMIX-SQL. The best I have been able to do is to unload the table Well, you can build a CLUSTER INDEX on the field in question, and then drop it. Can you say kludge? Sure you can, it's easy... Chris Hermansen Timberline Forest Inventory Consultants Voice: 1 604 733 0731 302 - 958 West 8th Avenue FAX: 1 604 733 0634 Vancouver B.C. CANADA uunet!ubc-cs!van-bc!tacitus!clh V5Z 1E5 clh@tfic.bc.ca -or- Chris_Hermansen@mtsg.ubc.ca May you work in an interesting place.
john@anasaz.UUCP (John Moore) (02/13/90)
In article <1990Feb12.162507.612@aqdata.uucp> sullivan@aqdata.uucp (Michael T. Sullivan) writes:
]From article <6548@uhccux.uhcc.hawaii.edu>, by richardj@uhccux.uhcc.hawaii.edu (Richard Jablonka):
]>
]> I want to SORT an informix table. I only need to do it once. The problem is
]> making the names come up in the right order in an entry form. Unfortunately
]> in an entry form you can only state the table name and you can't use the where
]> or order by clause.
]
]My guess is that you index the order you want and make that a clustering
]index. Since perform appears to show rows in their raw table order, this
]will probably do what you want.
]--
Informix has "implicit order-by." If you have an index on the table,
and you force it to use the index by including a reference to it
in the WHERE clause, it will return the rows in order of the index,
even without an order-by clause.
If you really want the raw table sorted, the act of creating a
CLUSTERED index does a physical sort on the table.
--
John Moore (NJ7E) mcdphx!anasaz!john asuvax!anasaz!john
(602) 951-9326 (day or eve) long palladium, short petroleum
7525 Clearwater Pkwy, Scottsdale, AZ 85253
Support ALL of the bill of rights, INCLUDING the 2nd amendment!rbp@investor.pgh.pa.us (Bob Peirce #305) (02/15/90)
In article <6548@uhccux.uhcc.hawaii.edu> richardj@uhccux.UUCP (Richard Jablonka) writes: > >I want to SORT an informix table. I only need to do it once. The problem is >making the names come up in the right order in an entry form. Unfortunately The idea of unloading, sorting and reloading, mentioned in another follow-up might work if you never plan to add any more records to the table. I have never tried it. On the other hand, being equally frustrated with this shortcoming of sperform, I have observed that certain queries do seem to order the output. I say seem, because I did not confirm this before posting. At any rate, numeric ranges (nnn:nnn) seem to get ordered as do some string searches using wild-cards. Perhaps "Dr. Scump" could comment. -- Bob Peirce, Pittsburgh, PA 412-471-5320 ...!uunet!pitt!investor!rbp rbp@investor.pgh.pa.us
romwa@gpu.utcs.utoronto.ca (Royal Ontario Museum) (02/16/90)
In article <1990Feb11.084522.12120@halexii.uucp> greg@halexii.UUCP (Gregory F. Hogg) writes: >In article <6548@uhccux.uhcc.hawaii.edu> richardj@uhccux.UUCP (Richard Jablonka) writes: >> >>I want to SORT an informix table. I only need to do it once. The problem is >>making the names come up in the right order in an entry form. Unfortunately > > As fas as I know there is no way todo what you want in >INFORMIX-SQL. The best I have been able to do is to unload the table >ordered the way you want and then reload it. This way the records are >in the dbtable allready ordered. > There is an index facility in isql thatputs the records in sorted order on the disk. I'm not sure of how m{uch overhead this puts on the database, but it may solve your problem. Without the 4GL this is the only way I know to do this. Mark T. Dornfeld Royal Ontario Museum 100 Queens Park Toronto, Ontario, CANADA M5S 2C6 mark@utgpu!rom - or - romwa@utgpu