Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
Last revision Both sides next revision
dev:sql:home [2010/07/07 19:59]
els
dev:sql:home [2016/06/28 22:38]
127.0.0.1 external edit
Line 1: Line 1:
 {{page>:​top_add&​nofooter&​noeditbtn}} {{page>:​top_add&​nofooter&​noeditbtn}}
 ~~NOTOC~~ ~~NOTOC~~
-====== Omnidex SQL Quick Reference ======+====== Omnidex SQL Statement ​Quick Reference ======
  
-^                                           ​Omnidex SQL Statements ​                                                                                                                                   ^^^ +[[dev:​sql:​home|Statements]] | [[dev:sql:functions|Functions]] | [[dev:sql:datatypes|Datatypes]] | [[dev:sql:examples ​Examples]]
-|**Connections** ​                                                  ​|**Data Definition** ​                                              ​|**Omnidex Index Queries** ​                                        | +
-|[[dev:sql:​statements:​connect:home|CONNECT]]                       ​|[[dev:sql:statements:​create_environment:​home|CREATE ENVIRONMENT]] |[[dev:sql:statements:​qualify:​home|QUALIFY]]                       | +
-|[[dev:sql:statements:​disconnect:​home|DISCONNECT]]                 |[[dev:​sql:​statements:​create_database:​home|CREATE DATABASE]] ​      ​|[[dev:​sql:​statements:​join:​home|JOIN]] ​                            | +
-|[[dev:​sql:​statements:​set:​home|SET]] ​                              ​|[[dev:​sql:​statements:​create_table:​home|CREATE TABLE]] ​            ​| ​                                                                 | +
-|                                                                  |[[dev:​sql:​statements:​create_index:​home|CREATE INDEX]] ​            ​| ​                                                                 | +
-|**Queries and Exports** ​                                          ​|[[dev:​sql:​statements:​create_index_group:​home|CREATE INDEX GROUP]] |**Maintenance** ​                                                  | +
-|[[dev:​sql:​statements:​select:​home|SELECT]] ​                        ​|[[dev:​sql:​statements:​drop_environment:​home|DROP ENVIRONMENT]] ​    ​|[[dev:​sql:​statements:​update_indexes:​home|UPDATE INDEXES]] ​        | +
-|[[dev:​sql:​statements:​export:​home|EXPORT]] ​                        ​|[[dev:​sql:​statements:​drop_database:​home|DROP DATABASE]] ​          ​|[[dev:​sql:​statements:​update_rollups:​home|UPDATE ROLLUPS]] ​        | +
-|[[dev:​sql:​statements:​open_cursor:​home|OPEN CURSOR]] ​              ​|[[dev:​sql:​statements:​drop_table:​home|DROP TABLE]] ​                ​|[[dev:​sql:​statements:​update_statistics:​home|UPDATE STATISTICS]] ​  | +
-|[[dev:​sql:​statements:​close_cursor:​home|CLOSE CURSOR]] ​            ​|[[dev:​sql:​statements:​drop_index:​home|DROP INDEX]] ​                ​|[[dev:​sql:​statements:​update_text:​home|UPDATE TEXT]] ​              | +
-|                                                                  |[[dev:​sql:​statements:​drop_index_group:​home|DROP INDEX GROUP]] ​    ​|[[dev:​sql:​statements:​partition:​home|PARTITION]] ​                  | +
-|**Updates** ​                                                      ​|[[dev:​sql:​statements:​attach:​home|ATTACH]] ​                        ​|[[dev:​sql:​statements:​load_ofx:​home|LOAD OFX]]                     | +
-|[[dev:​sql:​statements:​insert:​home|INSERT]] ​                        ​|[[dev:​sql:​statements:​detatch:​home|DETATCH]] ​                      ​| ​                                                                 | +
-|[[dev:​sql:​statements:​delete:​home|DELETE]] ​                        ​|[[dev:​sql:​statements:​extract:​home|EXTRACT]] ​                      ​| ​                                                                 | +
-|[[dev:​sql:​statements:​update:​home|UPDATE]] ​                        ​| ​                                                                 |                                                                  | +
-|----------------------------------------------------------|---------------------------------------------------------|----------------------------------------------------------|+
  
-\\ +|< 95% 50% 50% >| 
-\\ +^                                           ​Omnidex SQL Statements ​                                                                  ​^^ 
-\\ +^**Connections** ​                                                  ^**Data Definition** ​                                              ^
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
-\\ +
- +
-^                                           ​Omnidex SQL Statements ​                               ^^ +
-|**Connections** ​                                                  |**Data Definition** ​                                              |+
 |[[dev:​sql:​statements:​connect:​home|CONNECT]] ​                      ​|[[dev:​sql:​statements:​create_environment:​home|CREATE ENVIRONMENT]] | |[[dev:​sql:​statements:​connect:​home|CONNECT]] ​                      ​|[[dev:​sql:​statements:​create_environment:​home|CREATE ENVIRONMENT]] |
 |[[dev:​sql:​statements:​disconnect:​home|DISCONNECT]] ​                ​|[[dev:​sql:​statements:​create_database:​home|CREATE DATABASE]] ​      | |[[dev:​sql:​statements:​disconnect:​home|DISCONNECT]] ​                ​|[[dev:​sql:​statements:​create_database:​home|CREATE DATABASE]] ​      |
 |[[dev:​sql:​statements:​set:​home|SET]] ​                              ​|[[dev:​sql:​statements:​create_table:​home|CREATE TABLE]] ​            | |[[dev:​sql:​statements:​set:​home|SET]] ​                              ​|[[dev:​sql:​statements:​create_table:​home|CREATE TABLE]] ​            |
-|                                                                  ​|[[dev:​sql:​statements:​create_index:home|CREATE INDEX]]             | +|[[dev:​sql:​statements:​attach:home|ATTACH]]                         ​|[[dev:​sql:​statements:​create_index:​home|CREATE INDEX]] ​            ​
-|**Queries and Exports** ​                                          |[[dev:​sql:​statements:​create_index_group:​home|CREATE INDEX GROUP]] | +|[[dev:​sql:​statements:​detach:home|DETACH]]                       ​|[[dev:​sql:​statements:​create_index_group:home|CREATE INDEX GROUP]] | 
-|[[dev:​sql:​statements:​select:home|SELECT]]                         ​|[[dev:​sql:​statements:​drop_environment:home|DROP ENVIRONMENT]]     ​+|                                                                  ​|[[dev:​sql:​statements:​drop_environment:home|DROP ENVIRONMENT]]     | 
-|[[dev:​sql:​statements:​export:home|EXPORT]]                         ​|[[dev:​sql:​statements:​drop_database:​home|DROP DATABASE]] ​          | +^**Queries and Exports** ​                                          |[[dev:​sql:​statements:​drop_database:​home|DROP DATABASE]] ​          | 
-|[[dev:​sql:​statements:​open_cursor:home|OPEN CURSOR]]               ​|[[dev:​sql:​statements:​drop_table:​home|DROP TABLE]] ​                | +|[[dev:​sql:​statements:​select:home|SELECT]]                         ​|[[dev:​sql:​statements:​drop_table:​home|DROP TABLE]] ​                | 
-|[[dev:​sql:​statements:​close_cursor:home|CLOSE CURSOR]]             ​|[[dev:​sql:​statements:​drop_index:​home|DROP INDEX]] ​                | +|[[dev:​sql:​statements:​export:home|EXPORT]]                         ​|[[dev:​sql:​statements:​drop_index:​home|DROP INDEX]] ​                | 
-|                                                                  ​|[[dev:​sql:​statements:​drop_index_group:home|DROP INDEX GROUP]]     | +|[[dev:​sql:​statements:​open_cursor:home|OPEN CURSOR]]               ​|[[dev:​sql:​statements:​drop_index_group:home|DROP INDEX GROUP]]     ​
-|**Updates** ​                                                      |[[dev:​sql:​statements:​attach:home|ATTACH]]                         ​+|[[dev:​sql:​statements:​close_cursor:home|CLOSE CURSOR]]             ​|[[dev:​sql:​statements:​extract:home|EXTRACT]]                       | 
-|[[dev:​sql:​statements:​insert:home|INSERT]]                         ​|[[dev:​sql:​statements:​detatch:home|DETATCH]]                       | +|.                                                                ​                                                                 | 
-|[[dev:​sql:​statements:​delete:​home|DELETE]] ​                        ​|[[dev:​sql:​statements:​extract:​home|EXTRACT]] ​                      +^**Updates** ​                                                      ​^**Maintenance** ​                                                  ^ 
-|[[dev:​sql:​statements:​update:home|UPDATE]]                         |                                                                  | +|[[dev:​sql:​statements:​insert:home|INSERT]]                         ​|[[dev:​sql:​statements:​update_indexes:​home|UPDATE INDEXES]] ​        | 
-|                                                                  |**Maintenance** ​                                                  | +|[[dev:​sql:​statements:​delete:home|DELETE]]                         ​|[[dev:​sql:​statements:​update_rollups:​home|UPDATE ROLLUPS]] ​        | 
-|**Omnidex Index Queries** ​                                        |[[dev:​sql:​statements:​update_indexes:​home|UPDATE INDEXES]] ​        | +|[[dev:​sql:​statements:​update:home|UPDATE]]                         ​|[[dev:​sql:​statements:​update_statistics:​home|UPDATE STATISTICS]] ​  |
-|[[dev:​sql:​statements:​qualify:home|QUALIFY]]                       ​|[[dev:​sql:​statements:​update_rollups:​home|UPDATE ROLLUPS]] ​        | +
-|[[dev:​sql:​statements:​join:home|JOIN]]                             ​|[[dev:​sql:​statements:​update_statistics:​home|UPDATE STATISTICS]] ​  |+
 |                                                                  |[[dev:​sql:​statements:​update_text:​home|UPDATE TEXT]] ​              | |                                                                  |[[dev:​sql:​statements:​update_text:​home|UPDATE TEXT]] ​              |
-|                                                                  ​|[[dev:​sql:​statements:​partition:​home|PARTITION]] ​                  | +^**Omnidex Index Queries** ​                                        |[[dev:​sql:​statements:​partition:​home|PARTITION]] ​                  | 
-|                                                                  |[[dev:​sql:​statements:​load_ofx:​home|LOAD OFX]]                     | +|[[dev:​sql:​statements:​qualify:​home|QUALIFY]]                       ​|[[dev:​sql:​statements:​load_ofx:home|LOAD OFX]]                     ​
-|----------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------| +|[[dev:​sql:​statements:​join:​home|JOIN]]                             ​|[[dev:​sql:​statements:​install_indexes:home|INSTALL INDEXES]]       ​
- +                                                                 |[[dev:​sql:​statements:​remove_indexes:​home|REMOVE INDEXES]]         ​| ​                                                                  
- +\\ 
-{{page>:​sql_odxsql_bar&​nofooter&​noeditbtn}} +\\
- +
- +
-===== Omnidex SQL Statements and OdxSQL Commands ===== +
- +
-^ DML ^| [[dev:​sql:​statements:​select:​home |Select ]]| [[dev:​sql:​statements:​insert:​home |Insert ]] | [[dev:​sql:​statements:​delete:​home |Delete ]] | [[dev:​sql:​statements:​update:​home |Update ]] | [[dev:​sql:​statements:​qualify:​home |Qualify ​]] +
-^ Access ^| [[dev:​sql:​statements:​connect:home |Connect *]] | [[dev:​sql:​statements:​disconnect:​home | Disconnect *]] | [[dev:​sql:​statements:​attach:​home | Attach ]] | [[dev:​sql:​statements:​detach:​home | Detach ]] | [[dev:​sql:​statements:​set:​home | Set ]] | +
-^ DDL Create ^| [[dev:​sql:​statements:​create_environment:​home | Create Environment ]] | [[dev:​sql:​statements:​create_database:​home | Create Database ]] | [[dev:​sql:​statements:​create_table:​home | Create Table ]] | [[dev:​sql:​statements:​create_index:​home | Create Index ]] | [[dev:​sql:​statements:​create_index_group:​home | Create Index Group ]] | +
-^ DDL Drop  ^| [[dev:​sql:​statements:​drop_environment:​home | Drop Environment ]] | [[dev:​sql:​statements:​drop_database:​home |Drop Datebase ]] | [[dev:​sql:​statements:​drop_table:​home | Drop Table ]] | [[dev:​sql:​statements:​drop_index:​home | Drop Index ]] | [[dev:​sql:​statements:​drop_index_group | Drop Index Group ]] | +
-^ Syncing ^| [[dev:​sql:​statements:​update_indexes:​home | Update Indexes ]] | [[dev:​sql:​statements:​update_text:​home | Update Text ]] | [[dev:​sql:​statements:​update_statistics:​home | Update Statistics ]] | [[dev:​sql:​statements:​update_rollups:​home | Update Rollups ]] | [[dev:​sql:​statements:​update_rollup_table:​home | Update Rollup Table ]] | +
-^ Special ^| [[dev:​sql:​statements:​export:​home | Export ]] | [[dev:​sql:​statements:​Partition:​home | Partition ]] | [[dev:​sql:​statements:​join:​home | Join ]] | [[dev:​sql:​statements:​open_cursor:home | Open Cursor ]] | [[dev:​sql:​statements:​close_cursor:​home | Close Cursor ​]]| +
-^ Other ^[[dev:​sql:​statements:​install_indexes:​home | Install Indexes ]] | [[ dev:​sql:​statements:​remove_indexes:​home | Remove Indexes ​]] | [[dev:​sql:​statements:​load_ofx:​home | Load OFX ]]| [[dev:​sql:​statements:​command:​home | Command ]] | [[dev:​odxsql:​commands:​extract:​home | Extract *]]| +
- +
-* Statement is only available from OdxSQL. +
- +
-===== Omnidex SQL Standard Functions ===== +
-| [[ dev:​sql:​functions:​abs | ABS ]]| [[ dev:​sql:​functions:​case | CASE ]] | [[ dev:​sql:​functions:​cast | CAST ]] |[[ dev:​sql:​functions:​character_length:​home | CHAR[ACTER]_LENGTH ]] |[[ dev:​sql:​functions:​coalesce | COALESCE ]] | [[ dev:​sql:​functions:​concatenation | || (Concatenation) ]] |  +
-|[[ dev:​sql:​functions:​current_date | CURRENT_DATE ]] | [[ dev:​sql:​functions:​current_time | CURRENT_TIME]] | [[ dev:​sql:​functions:​current_timestamp | CURRENT_TIMESTAMP]] |[[ dev:​sql:​functions:​current_user | CURRENT_USER]] | [[ dev:​sql:​functions:​extract | EXTRACT]] | [[ dev:​sql:​functions:​lower | LOWER]] |  +
-| [[ dev:​sql:​functions:​mod | MOD ]] | [[ dev:​sql:​functions:​position | POSITION]] | [[ dev:​sql:​functions:​power | POWER ]] | [[ dev:​sql:​functions:​round | ROUND ]] | [[ dev:​sql:​statements:​select:​sample | SAMPLE ]] | [[ dev:​sql:​functions:​substring | SUBSTRING]] |  +
-| [[ dev:​sql:​functions:​session_user | SESSION_USER]] | [[ dev:​sql:​functions:​system_user | SYSTEM_USER ]] | [[ dev:​sql:​functions:​trim | TRIM ]] | [[ dev:​sql:​functions:​trunc | TRUNC ]] | [[ dev:​sql:​functions:​upper | UPPER ]] | [[ dev:​sql:​functions:​user | USER ]] |  +
- +
-===== Omnidex Extended Functions ===== +
-| [[ dev:​sql:​functions:​calc_date | $CALC_DATE ]] | [[ dev:​sql:​functions:​col_length | $COL[UMN]_LENGTH ]] | [[ dev:​sql:​functions:​contains:​home | $CONTAINS ]] | [[ dev:​sql:​functions:​context:​home | $CONTEXT ]] | [[ dev:​sql:​functions:​convert | $CONVERT ]] | [[ dev:​sql:​functions:​compare_dates | $COMPARE_DATES]] | +
-|[[ dev:​sql:​functions:​current_row | $CURRENT_ROW ]] | [[ dev:​sql:​functions:​distance | $DISTANCE ]] | [[ dev:​sql:​functions:​external | $EXTERNAL ]] | [[ [[ dev:​sql:​functions:​highlight_criteria:​home | $HIGHLIGHT_CRITERIA ]] | [[ dev:​sql:​functions:​highlight_keywords:​home | $HIGHLIGHT_KEYWORDS ]] | [[ dev:​sql:​functions:​ifnull | $IFNULL ]] | +
-| [[ dev:​sql:​functions:​lj | $LJ ]] |[[ [[ dev:​sql:​functions:​lookup:​home | $LOOKUP ]] | [[ dev:​sql:​functions:​lpad | $LPAD ]] | [[ dev:​sql:​functions:​mod | $MOD ]]  | [[ dev:​sql:​functions:​proper | $PROPER ]] | [[ dev:​sql:​functions:​random | $RANDOM ]] | +
-| [[ dev:​sql:​functions:​rj | $RJ ]]  | [[ dev:​sql:​functions:​rpad | $RPAD ]] | [[ dev:​sql:​functions:​score | $SCORE ]] | [[ dev:​sql:​functions:​soundex | $SOUNDEX ]] |  +
-===== Oracle Functions ===== +
-Use the CREATE ENVIRONMENT SQL_SYNTAX setting or the SET SQL_SYNTAX setting to activate. +
-| [[ dev:​sql:​orafunctions:​chr | CHR ]] | [[ dev:​sql:​orafunctions:​instr | INSTR ]] | [[ dev:​sql:​orafunctions:​new_time | NEW_TIME ]] | [[ dev:​sql:​orafunctions:​nvl | NVL ]] | [[ dev:​sql:​orafunctions:​substr | SUBSTR ]] | [[ dev:​sql:​orafunctions:​sys_extract_utc | SYS_EXTRACT_UTC ]] | [[ dev:​sql:​orafunctions:​sysdate | SYSDATE ]] | [[ dev:​sql:​orafunctions:​systimestamp | SYSTIMESTAMP ]] | [[ dev:​sql:​orafunctions:​to_date | TO_DATE ]] | [[ dev:​sql:​orafunctions:​to_number | TO_NUMBER ]] | +
-===== Omnidex Data Types ===== +
-^See Also:​^[[dev:​sql:​datatypes:​home | List of Datatypes]]^ +
-=== Standard Datatypes === +
- +
-Omnidex supports the following standard datatypes:​ +
- +
-^ Datatype ​               ^ Description ​                                                 ^ +
-| CHARACTER(//​n//​) ​       | Space-filled character string of //n// characters ​           | +
-| CHAR(//​n//​) ​            | Synonym for CHARACTER(//​n//​) ​                                | +
-| C STRING(//​n//​) ​        | Null-terminated character string of //n// characters ​        | +
-| VARCHAR(//​n//​)* ​        | String of max //n// characters, with length variable ​        | +
-| CLOB(//​n//​)* ​           | Character large object of max //n// characters, with length variable | +
-| [SIGNED] TINYINT ​       | 1-byte, signed integer ( -128 to 127 )                       | +
-| UNSIGNED TINYINT ​       | 1-byte, unsigned integer ( 0 to 255 )                        | +
-| [SIGNED] SMALLINT ​      | 2-byte, signed integer ( -32768 to 32767 )                   | +
-| UNSIGNED SMALLINT ​      | 2-byte, unsigned integer ( 0 to 65535 )                      | +
-| [SIGNED] INTEGER ​       | 4-byte, signed integer ( 2147483648 to 2147483647 )          | +
-| UNSIGNED INTEGER ​       | 4-byte, unsigned integer ( 0 to 4294967295 )                 | +
-| [SIGNED] BIGINT ​        | 8-byte, signed integer ( %%-2^63 to 2^63-1%% )               | +
-| UNSIGNED BIGINT ​        | 8-byte, unsigned integer ( %%0 to 2^64-1%% )                 | +
-| FLOAT                   | IEEE four-byte, single-precision floating point              | +
-| DOUBLE ​                 | IEEE eight-byte, double-precision floating point             | +
-| DATE                    | 10-byte ANSI date                                            | +
-| TIME                    | 11-byte ANSI time                                            | +
-| DATETIME ​               | 24-byte ANSI datetime ​                                       | +
-| ASCII DATE              | 8-byte ASCII date ( YYYYMMDD )                               | +
-| ASCII DATE(6) ​          | 6-byte ASCII date ( YYMMDD )                                 | +
-| ODBC DATE               | 6-byte ODBC proprietary date                                 | +
-| ODBC TIME               | 6-byte ODBC proprietary time                                 | +
-| ODBC DATETIME ​          | 6-byte ODBC proprietary datetime ​                            | +
-| ORACLE DATE             | 7-byte Oracle proprietary date                               | +
-| ORACLE TIME             | 7-byte Oracle proprietary time                               | +
-| ORACLE DATETIME ​        | 7-byte Oracle proprietary datetime ​                          | +
-| OMNIDEX DATE(//​n//​) ​    | Omnidex proprietary date supporting //n// digits of YYYYMMDD | +
-| OMNIDEX TIME(//​n//​) ​    | Omnidex proprietary time supporting //n// digits of HHMMSSNN | +
-| OMNIDEX DATETIME(//​n//​) | Omnidex proprietary datetime supporting //n// digits of YYYYMMDDHHMMSSNN | +
-| * Omnidex recommends use of the CHARACTER and C STRING datatypes rather than VARCHAR and CLOB due to the requirement of a length variable. || +
- +
-=== National Character Datatypes === +
  
-Omnidex also provides partial support for National Character Datatypes (sometimes called +====== ​Omnidex SQL Statement Descriptions =======
-Unicode datatypes or wide characters). ​ The Omnidex SQL Engine will process these  +
-datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these  +
-datatypes.+
  
-^ Datatype ​                 ^ Description ​                                               ^ +Omnidex ​SQL Statements can be issued through any standard ODBC and JDBC connection, ​and through ​the OdxSQL program.  ​
-| NATIONAL CHARACTER(//​n//​) | Space-filled character string of //n// characters ​         | +
-| NATIONAL CHAR(//​n//​) ​     | Synonym for CHARACTER(//​n//​) ​                              | +
-| NATIONAL C STRING(//​n//​) ​ | Null-terminated character string of //n// characters ​      | +
-| NATIONAL VARCHAR(//​n//​)* ​ | String of max //n// characters, with length variable ​      | +
-| NATIONAL CLOB(//​n//​)* ​    | Character large object of max //n// characters, with length variable | +
-| * Omnidex ​recommends use of the NATIONAL CHARACTER ​and NATIONAL C STRING datatypes rather than NATIONAL VARCHAR ​and NATIONAL CLOB due to the requirement of a length variable.  ​||+
  
 +|< 95% 25% 75% >|
 +^Statement ​ ^Description ​ ^
 +|  ||
 +^**Connections** ​ ^^
 +| [[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. |
 +| [[dev:​sql:​statements:​attach:​home|ATTACH]] | Attach an Omnidex Standalone Table (OST) to a connected Omnidex Environment File. |
 +| [[dev:​sql:​statements:​detach:​home|DETACH]] | Detach an Omnidex Standalone Table (OST) from a connected Omnidex Environment. |
 +|  ||
 +^**Queries and Exports** ​ ^|
 +| [[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. |
 +|  ||
 +^**Updates** ​ ^|
 +| [[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 Index Queries** ​ ^|
 +| [[dev:​sql:​statements:​qualify:​home|QUALIFY]] | Qualify index counts using Omnidex criteria. |
 +| [[dev:​sql:​statements:​join:​home|JOIN]] | Join tables using Omnidex Indexes. |
 +|  ||
 +^**Data Definition** ​ ^|
 +| [[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:​home | DROP INDEX GROUP]] | Removes a named Index Group from the Omnidex metadata. |
 +|  ||
 +^**Maintenance** ​ ^|
 +| [[dev:​sql:​statements:​update_indexes:​home|UPDATE INDEXES]] | Update Omnidex Indexes. |
 +| [[dev:​sql:​statements:​update_rollup_table:​home|UPDATE ROLLUP TABLE]] | 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. | 
 +| [[ dev:​sql:​statements:​install_indexes:​home | INSTALL INDEXES ]]| Specifies the Omnidex indexes into the Omnidex XML Environment. No indexing is performed. |
 +| [[ dev:​sql:​statements:​remove_indexes:​home | REMOVE INDEXES]]| Removes the Omnidex indexes from the Omnidex XML Environment. |
  
-===== Create a simple functioning Environment with an Omnidex index ===== 
-<​code>​ 
-os> odxsql 
-> CREATE ENVIRONMENT IN "​myenv.xml";​ 
-> CREATE DATABASE mydb TYPE FLATFILE IN "​myenv.xml";​ 
-> CREATE TABLE mytable PHYSICAL "​test1" ​ 
-       ​(myseq integer, mytext char(60) quicktext) ​ 
-       IN "​myenv.xml";​ 
-> CONNECT myenv.xml; 
-> UPDATE INDEXES; 
-> SELECT * FROM mytable where mytext = '<​some criteria>';​ 
-> DISCONNECT; 
-> EXIT 
-os> 
-</​code>​ 
-===== Learning Omnidex ===== 
-  * [[ tutorials:​simple_flatfile | Creating a very simple flat file Environment and doing retrievals. ]] 
-  * [[ tutorials:​simple_flatfile_java_JDBC | Writing a simple JDBC program to access the simple flatfile Environment. ]] 
-  
  
  
 {{page>:​bottom_add&​nofooter&​noeditbtn}} {{page>:​bottom_add&​nofooter&​noeditbtn}}
 
Back to top
dev/sql/home.txt · Last modified: 2020/11/30 18:42 by doc