This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
dev:sql:statements:home [2010/06/14 17:28] tdo |
dev:sql:statements:home [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 4: | Line 4: | ||
====== Omnidex SQL Reference ====== | ====== Omnidex SQL Reference ====== | ||
{{page>:sql_bar&nofooter&noeditbtn}} | {{page>:sql_bar&nofooter&noeditbtn}} | ||
- | The following Omnidex SQL Statements can be used by OdxSQL or an ODBC or JDBC application. | + | ===== Omnidex SQL Statements (alphabetical order) ===== |
- | ===== Omnidex SQL SELECT and Data Manipulation Statements ===== | + | |
- | + | ||
- | Omnidex SQL supports SQL's SELECT and Data Manipulation Statements (INSERT, UPDATE, and DELETE). | + | |
- | + | ||
- | | [[dev:sql:statements:select:home|SELECT]] | Peform a Query Specification and SELECT rows. | | + | |
- | | [[dev:sql:statements:insert:home|INSERT]] | Insert rows into a Table. | | + | |
- | | [[dev:sql:statements:update:home|UPDATE]] | Update rows in a Table. | | + | |
- | | [[dev:sql:statements:delete:home|DELETE]] | Delete rows in a Table. | | + | |
- | ===== Omnidex Connection Statements ===== | + | |
- | Omnidex supports statements to establish connections and set processing options for the connection. | + | |
- | + | ||
- | | [[dev:sql:statements:connect:home|CONNECT]] | Connect to an Omnidex Environment. | | + | |
- | | [[dev:sql:statements:disconnect:home|DISCONNECT]] | Disconnect from a connected Omnidex Environment. | | + | |
- | | [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | | + | |
- | ===== Omnidex SQL Data Definition Language (DDL) Statements ===== | + | |
- | Omnidex SQL has statements that create and drop objects within an Omnidex Environment. | + | |
- | + | ||
- | Also see the OdxSQL Extract command to generate DDL statements from an existing Omnidex Environment. | + | |
- | + | ||
- | ^ DDL Statement ^ Description ^ | + | |
- | | [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment into a named physical operating system file. | | + | |
- | | [[dev:sql:statements:create_database:home|CREATE DATABASE]] | Create a Database within an Omnidex Environment. | | + | |
- | | [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a Table within a Database in the Omnidex metadata or an Omnidex or RDBMS temporary table. | | + | |
- | | [[dev:sql:statements:create_index:home|CREATE INDEX]] | Specify an Index in the Omnidex metadata but will not physically create the index. | | + | |
- | | [[dev:sql:statements:create_index_group:home | CREATE INDEX GROUP ]] | Specify an Index Group within the Omnidex metadata. | | + | |
- | | [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Deletes the Omnidex Metadata Environment by physically deleting the operating system file. | | + | |
- | | [[dev:sql:statements:drop_database:home|DROP DATABASE]] | Remove the named Database from the Omnidex metadata. | | + | |
- | | [[dev:sql:statements:drop_table:home|DROP TABLE]] | Remove a table from the Omnidex metadata or deletes a previously created temporary table. | | + | |
- | | [[dev:sql:statements:drop_index:home|DROP INDEX]] | Removes an index from the Omnidex metadata. | | + | |
- | | [[dev:sql:statements:drop_index_group | DROP INDEX GROUP]] | Removes a named Index Group from the Omnidex metadata. | | + | |
- | | [[dev:sql:statements:attach:home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment for queries and manipulation. | | + | |
- | | [[dev:sql:statements:detach:home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. | | + | |
- | + | ||
- | + | ||
- | ===== Omnidex SQL Query and Export Statements ===== | + | |
- | | [[dev:sql:statements:select:home|SELECT]] | Peform a Query Specification and SELECT rows. | | + | |
- | | [[dev:sql:statements:export:home|EXPORT]] | Export rows or rowids to an external file. | | + | |
- | | [[dev:sql:statements:open_cursor:home |OPEN CURSOR]] | Open a Cursor. | | + | |
- | | [[dev:sql:statements:close_cursor:home|CLOSE CURSOR]] | Close a previously opened cursor. | | + | |
- | + | ||
- | + | ||
- | ===== Omnidex SQL Index and Processing Maintenance Statements ===== | + | |
- | Omnidex supports several statements to synchronize the Omnidex indexes, rollup tables and internal statistics. | + | |
- | + | ||
- | | [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | Update Omnidex Indexes. | | + | |
- | | [[dev:sql:statements:update_rollup:home|UPDATE ROLLUP]] | Update Rollup Table with summarized information. | | + | |
- | | [[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]] | Update all Rollup Tables for an Omnidex Database. | | + | |
- | | [[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]] | Update Statistics Information. | | + | |
- | | [[dev:sql:statements:home:update_text|UPDATE TEXT]] | Update Textual Statistics. | | + | |
- | | [[dev:sql:statements:partition:home|PARTITION]] | Partition a table into smaller sub tables based on criteria. | | + | |
- | | [[dev:sql:statements:load_ofx:home|LOAD OFX]] | Load Indexes for a table. | | + | |
- | + | ||
- | + | ||
- | ===== Alphabetical List of Omnidex SQL Statements ===== | + | |
^Statement^ Description ^ | ^Statement^ Description ^ | ||
Line 68: | Line 14: | ||
| [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | | | [[dev:sql:statements:create_environment:home|CREATE ENVIRONMENT]] | Create an Omnidex Environment. | | ||
| [[dev:sql:statements:create_index:home|CREATE INDEX]] | Create an Index. | | | [[dev:sql:statements:create_index:home|CREATE INDEX]] | Create an Index. | | ||
+ | | [[dev:sql:statements:create_index_group:home|CREATE INDEX GROUP]] | Create an Omnidex Index Group. | | ||
| [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a table within an Omnidex Environment or create a temp table in the underlying RDBMS. | | | [[dev:sql:statements:create_table:home|CREATE TABLE]] | Create a table within an Omnidex Environment or create a temp table in the underlying RDBMS. | | ||
| [[dev:sql:statements:delete:home|DELETE]] | Delete a row or rows of data. | | | [[dev:sql:statements:delete:home|DELETE]] | Delete a row or rows of data. | | ||
Line 75: | Line 22: | ||
| [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Drop an Omnidex XML Environment and purge the underlying operating system file. | | | [[dev:sql:statements:drop_environment:home|DROP ENVIRONMENT]] | Drop an Omnidex XML Environment and purge the underlying operating system file. | | ||
| [[dev:sql:statements:drop_index:home|DROP INDEX]] | Drop an Omnidex Index. | | | [[dev:sql:statements:drop_index:home|DROP INDEX]] | Drop an Omnidex Index. | | ||
+ | | [[dev:sql:statements:drop_index_group:home|DROP INDEX GROUP]] | Drop an Omnidex Index Group. | | ||
| [[dev:sql:statements:drop_table:home|DROP TABLE]] | Drop a table created with a CREATE TABLE or SELECT INTO statement. | | | [[dev:sql:statements:drop_table:home|DROP TABLE]] | Drop a table created with a CREATE TABLE or SELECT INTO statement. | | ||
| [[dev:sql:statements:export:home|EXPORT]] | Export rows or rowids to an external file. | | | [[dev:sql:statements:export:home|EXPORT]] | Export rows or rowids to an external file. | | ||
| [[dev:sql:statements:insert:home|INSERT]] | Insert a row of data. | | | [[dev:sql:statements:insert:home|INSERT]] | Insert a row of data. | | ||
+ | | [[dev:sql:statements:install_indexes:home | INSTALL INDEXES ]]| Specify the Omnidex indexes but do not synchronize the indexes to the underlying data.| | ||
| [[dev:sql:statements:join:home|JOIN]] | Manually join two tables for a joined query | | | [[dev:sql:statements:join:home|JOIN]] | Manually join two tables for a joined query | | ||
| [[dev:sql:statements:load_ofx:home|LOAD OFX]] | Load an OFX file. | | | [[dev:sql:statements:load_ofx:home|LOAD OFX]] | Load an OFX file. | | ||
Line 83: | Line 32: | ||
| [[dev:sql:statements:partition:home|PARTITION]] | Partition Data based on criteria. | | | [[dev:sql:statements:partition:home|PARTITION]] | Partition Data based on criteria. | | ||
| [[dev:sql:statements:qualify:home|QUALIFY]] | Qualify rows in a query. Typically superseded by SELECT| | | [[dev:sql:statements:qualify:home|QUALIFY]] | Qualify rows in a query. Typically superseded by SELECT| | ||
+ | | [[dev:sql:statements:remove_indexes:home | REMOVE INDEXES ]] | Remove the Omnidex Indexes from the the Omnidex Envrionment.| | ||
| [[dev:sql:statements:select:home|SELECT]] | Select rows , aggregate rows, using Omnidex enhanced criteria. | | | [[dev:sql:statements:select:home|SELECT]] | Select rows , aggregate rows, using Omnidex enhanced criteria. | | ||
| [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | | | [[dev:sql:statements:set:home|SET]] | Set Omnidex SQL processing options. | | ||
| [[dev:sql:statements:update:home|UPDATE]] | Update rows in a table. | | | [[dev:sql:statements:update:home|UPDATE]] | Update rows in a table. | | ||
| [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | Update all Omnidex Indexes. | | | [[dev:sql:statements:update_indexes:home|UPDATE INDEXES]] | Update all Omnidex Indexes. | | ||
- | | [[dev:sql:statements:update_rollup:home|UPDATE ROLLUP]] | Update Rollup Tables with new calculations. | | + | | [[dev:sql:statements:update_rollup_table:home|UPDATE ROLLUP TABLE]] | Update Rollup Tables with new calculations. | |
| [[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]] | Update all Rollup Tables with new calculations. | | | [[dev:sql:statements:update_rollups:home|UPDATE ROLLUPS]] | Update all Rollup Tables with new calculations. | | ||
| [[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]] | Update the Omnidex Statistics information. | | | [[dev:sql:statements:update_statistics:home|UPDATE STATISTICS]] | Update the Omnidex Statistics information. | | ||
| [[dev:sql:statements:update_text:home|UPDATE TEXT]] | Update Omnidex Text information. | | | [[dev:sql:statements:update_text:home|UPDATE TEXT]] | Update Omnidex Text information. | | ||
+ | |||
{{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} |