OpenEdge SQL Reference

https://docs.progress.com/bundle/openedge-sql-reference
Admin
Provides comprehensive reference documentation and guidelines for working with SQL within the...
Tokens:218,846
Snippets:2,253
Trust Score:10
Update:1 day ago
Show doc for...
Tokens:
Raw
### CREATE SUPER-TENANT Example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/CREATE-SUPER-TENANT.html

Example that creates a super-tenant named 'tenantadmin'.

```sql
CREATE SUPER_TENANT tenantadmin;
```

--------------------------------

### SHOW CATALOGS PRO_NAME example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-CATALOGS.html

Example that returns the names of currently available catalogs.

```sql
SHOW CATALOGS PRO_NAME;
```

--------------------------------

### SHOW ALL TENANT FOR TABLE example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-TENANT.html

Example that returns the available tenants of the table pub.mtcustomer using the SHOW ALL TENANT FOR TABLE statement.

```sql
SHOW ALL TENANT FOR TABLE pub.mtcustomer;
```

--------------------------------

### DROP DOMAIN example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DROP-DOMAIN.html

Example showing how to drop a domain named 'jasper'.

```sql
DROP DOMAIN jasper;
```

--------------------------------

### DROP TABLE example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DROP-TABLE.html

Example of the DROP TABLE statement that deletes the 'customer' table.

```sql
DROP TABLE customer ;
```

--------------------------------

### SELECT NEXT_DAY example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/NEXT_DAY.html

Example query illustrating the NEXT_DAY function, returning the next Monday after each order_date from the orders table.

```sql
SELECT NEXT_DAY (order_date, 'MONDAY') FROM orders ;
```

--------------------------------

### DROP SEQUENCE example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DROP-SEQUENCE.html

Example of dropping the sequence named customer in the pub schema.

```sql
DROP SEQUENCE pub.customer;
```

--------------------------------

### SELECT customer_sequence.CURRVAL example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/CURRVAL.html

Example query that returns the current value of the customer sequence from the pub schema.

```sql
SELECT customer_sequence.CURRVAL FROM pub.customer;
```

--------------------------------

### Quantified Predicate Example with ANY

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/Quantified-Predicate.html

Example using ANY with a subquery that counts orders per customer. The predicate is true if 10 is less than any of the counts.

```sql
10 < ANY ( SELECT COUNT(*) FROM order_tbl GROUP BY custid ; )
```

--------------------------------

### SELECT with WHERE clause example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/WHERE-clause.html

Example query that retrieves customer names, cities, and states for customers in New Mexico (State = 'NM').

```sql
SELECT Name, City, State FROM Customer WHERE State = 'NM' ;
```

--------------------------------

### LEAST function example query

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/LEAST.html

Example SQL query using LEAST to compare ADD_MONTHS(start_date, 10) with SYSDATE for each customer.

```sql
SELECT cust_no, last_name, LEAST (ADD_MONTHS (start_date, 10), SYSDATE) FROM customer ;
```

--------------------------------

### ASCII function example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/ASCII.html

Example query using the ASCII function to get the ASCII value of the first character of the PostalCode column from the Customer table.

```sql
SELECT ASCII ( PostalCode ) FROM Customer;
```

--------------------------------

### CONNECT AS CATALOG example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/CONNECT-AS-CATALOG.html

Connects the database 'customer' in directory '/usr/databases' as catalog 'mydb1'.

```sql
CONNECT 'usr/databases/customer' AS CATALOG mydb1;
```

--------------------------------

### SELECT LOCATE example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/LOCATE.html

Example query using LOCATE with two string literals and a start position of 1, returning 6. The output shows the result column labeled 'LOCATE(THIS' with value 61.

```sql
SELECT LOCATE('this', 'test this test', 1) FROM TEST;
```

```text
LOCATE(THIS, ------------ 61 record selected
```

--------------------------------

### SUFFIX with literal character and start position

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SUFFIX.html

Example query using SUFFIX with a literal character '.' and start position 6. The output shows the original columns and the result of SUFFIX(C1, 6, '.') for each row.

```sql
SELECT C1, C2, SUFFIX(C1, 6, '.') FROM T1;
```

```text
C1          C2          SUFFIX(C1,6,.
--          --          -------------
test.pref   .           pref
pref.test   s           2 records selected
```

--------------------------------

### INSERT function example with SELECT

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/INSERT_3.html

Example query using the INSERT function on the customer table. It deletes two characters from 'Goldman' starting at position 2 and inserts 'xx', resulting in 'Gxxan'. The output shows the result and the number of records selected.

```SQL
SELECT INSERT(last_name,2,4,'xx') FROM customer WHERE last_name = 'Goldman';
```

```Output
INSERT LAST_NAME,2,4,XX)
------------------------
Gxxan
1 record selected
```

--------------------------------

### SHOW ALL GROUP basic example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-GROUP.html

Returns basic information about all groups defined in the multi-tenant database.

```sql
SHOW ALL GROUP;
```

--------------------------------

### GET DIAGNOSTICS EXCEPTION example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/GET-DIAGNOSTICS-EXCEPTION.html

Extracts the SQLSTATE and message text into host variables declared in the DECLARE SECTION of an embedded SQL program.

```sql
GET DIAGNOSTICS EXCEPTION :num :sstate = RETURNED_SQLSTATE, :msgtxt = MESSAGE_TEXT ;
```

--------------------------------

### Generating unique customer numbers

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/CREATE-SEQUENCE.html

Example of creating a sequence to generate unique customer numbers for the pub.customer table, starting at 100 and incrementing by 1.

```sql
CREATE SEQUENCE pub.customer_sequence START WITH 100, INCREMENT BY 1, NOCYCLE;
```

--------------------------------

### EXECUTE statement example with PREPARE and COMMIT

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/EXECUTE.html

Example from the EXECUTE documentation showing how to prepare a dynamic SQL statement, execute it, and commit the transaction. The statement is prepared from a host variable sql_stmt_v and then executed.

```SQL
/* ** Process the non-SELECT input statement ** PREPARE the statement ** EXECUTE the prepared statement ** COMMIT WORK */
EXEC SQL PREPARE dynstmt FROM :sql_stmt_v ;
EXEC SQL EXECUTE dynstmt ;
EXEC SQL COMMIT WORK ;
```

--------------------------------

### SHOW ALL PARTITION FOR TABLE Pub.Customer

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-PARTITION.html

Returns basic information about all partitions defined on the table Pub.Customer.

```sql
SHOW ALL PARTITION FOR TABLE Pub.Customer;
```

--------------------------------

### GET DIAGNOSTICS example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/GET-DIAGNOSTICS.html

Extracts header information about the last SQL statement executed, assigning values to host variables declared in the DECLARE SECTION of an embedded SQL program.

```sql
GET DIAGNOSTICS :num = NUMBER, :cmdfunc = COMMAND_FUNCTION ;
```

--------------------------------

### PREFIX function example with table creation and inserts

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/PREFIX.html

Creates a table and inserts sample data to demonstrate the PREFIX function. The example shows how to use PREFIX with a literal character, a column value, and a character not found in the string.

```sql
create table prefix_table ( colstring varchar(20), colchar char(1) );
insert into prefix_table values ('string.with.dots', '.');
insert into prefix_table values ('string-with-dashes', '-');
select colstring, colchar, prefix(colstring, 1, '.') from prefix_table;
COLSTRING COLCHAR prefix(COLSTRING,1,.)
--------------------- --------------------- ----------------------
string.with.dots . string
string-with-dashes - string-with-dashes
select colstring, colchar, prefix(colstring, 1, colchar) from prefix_table;
COLSTRING COLCHAR prefix(COLSTRING,1,COLCHAR)
--------------------- --------------------- ----------------------------
string.with.dots . string
string-with-dashes - string
select colstring, colchar, prefix(colstring, 1, 'X') from prefix_table;
COLSTRING COLCHAR prefix(COLSTRING,1,X)
--------------------- --------------------- ----------------------
string.with.dots . string.with.dots
string-with-dashes - string-with-dashes
```

--------------------------------

### SUFFIX with column reference as character

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SUFFIX.html

Example query using SUFFIX with a column reference C2 as the character argument and start position 1. The output shows the original columns and the result of SUFFIX(C1, 1, C2) for each row.

```sql
SELECT C1, C2, SUFFIX(C1, 1, C2) FROM T1;
```

```text
C1          C2          SUFFIX(C1,1,C
--          --          -------------
test.pref   .           pref
pref.test   s           t
2 records selected
```

--------------------------------

### Example of BEGIN-END DECLARE SECTION

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/BEGIN-END-DECLARE-SECTION.html

Declares three short integer host variables (InvTransNum_v, Qty_v, OrderNum_v) inside a DECLARE SECTION for use in embedded SQL. The section starts with EXEC SQL BEGIN DECLARE SECTION and ends with EXEC SQL END DECLARE SECTION.

```SQL
EXEC SQL BEGIN DECLARE SECTION ;
short InvTransNum_v ;
short Qty_v ;
short OrderNum_v ;
EXEC SQL END DECLARE SECTION ;
```

--------------------------------

### Example: SYSDATE, SYSTIME, and SYSTIMESTAMP formats

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SYSTIMESTAMP.html

This example illustrates different formats for SYSDATE, SYSTIME, and SYSTIMESTAMP. It shows three SELECT statements and their corresponding outputs.

```sql
SELECT SYSDATE FROM test;
SYSDATE
-------
09/13/2003
1 record selected
```

```sql
SELECT SYSTIME FROM test;
SYSTIME
-------
14:44:07:000
1 record selected
```

```sql
SELECT SYSTIMESTAMP FROM test;
SYSTIMESTAMP
------------
2003-09-13 14:44:15:000
1 record selected
```

--------------------------------

### INSERT(string_exp1, start, length, string_exp2)

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/String-functions.html

Returns a string where `length` characters have been deleted from `string_exp1` beginning at `start` and `string_exp2` has been inserted at `start`.

```APIDOC
## INSERT(string_exp1, start, length, string_exp2)

### Description
Returns a string where `length` characters have been deleted from `string_exp1` beginning at `start` and `string_exp2` has been inserted into `string_exp1` beginning at `start`.

### Function Signature
`INSERT(string_exp1, start, length, string_exp2)`

### Parameters
- **string_exp1** (string) - Required - The original string expression.
- **start** (integer) - Required - The starting position for deletion and insertion. Must be a literal numeric value or result of another scalar function with underlying data type `SQL_TINYINT`, `SQL_SMALLINT`, or `SQL_INTEGER`.
- **length** (integer) - Required - The number of characters to delete.
- **string_exp2** (string) - Required - The string to insert.

### Returns
String - The resulting string.
```

--------------------------------

### SHOW ALL GROUP FOR TENANT example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-GROUP.html

Returns information about all groups that the specified tenant belongs to.

```sql
SHOW ALL GROUP FOR TENANT Consolidated_Freightways;
```

--------------------------------

### Throws Example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/Throws_26.html

An example of using the Throws clause with DhSQLException in an OpenEdge SQL stored procedure. The example creates a procedure that inserts a row and returns the row count.

```APIDOC
## Throws

### Description
Declares exceptions that a stored procedure may throw. The example uses DhSQLException.

### Syntax
CREATE PROCEDURE procedure_name() RESULT (result_column data_type) BEGIN ... END

### Example
CREATE PROCEDURE sis_rowCount() RESULT ( ins_recs INTEGER ) BEGIN SQLPStatement insert_test103 = new SQLPStatement ( "INSERT INTO test103 (fld1) values (17)"); insert_test103.execute(); SQLResultSet.set(1,new Long(insert_test103.rowCount())); SQLResultSet.insert(); END
```

--------------------------------

### Combined CONNECT, SET CONNECTION, and DISCONNECT workflow

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DISCONNECT.html

Illustrates a sequence of steps: connect with a named connection, connect to DEFAULT, disconnect DEFAULT, set the named connection current, and disconnect CURRENT. The example shows the effect on the current connection.

```c
/* ** 1. CONNECT TO 'connect_string' */ EXEC SQL CONNECT TO 'progress:T:localhost:6745:salesdb' AS 'conn_1' ; /* ** 2. CONNECT TO DEFAULT. This suspends the conn_1 connection ** and sets the DEFAULT connection current */ EXEC SQL CONNECT TO DEFAULT ; /* ** Application processing against the DEFAULT database */ . . . /* ** 3. DISCONNECT DEFAULT */ EXEC SQL DISCONNECT DEFAULT ; /* ** 4. Set the first connection, conn_1, current */ EXEC SQL SET CONNECTION conn_1 ; /* ** Application processing against the database in the connect_string */ . . . /* ** 5. DISCONNECT the conn_1 connection, which is the current connection. */ EXEC SQL DISCONNECT CURRENT ;
```

--------------------------------

### CONNECT statement examples

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/CONNECT.html

Three CONNECT statements: connecting to a database by name, using a complete connect string with TCP/IP, and connecting to the default environment-defined database.

```sql
CONNECT TO "salesdb" AS "sales_conn"; CONNECT TO "progress:T:localhost:custdb" AS "cust_conn"; CONNECT TO DEFAULT;
```

--------------------------------

### DROP PROCEDURE example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DROP-PROCEDURE.html

Example that drops the stored procedure named new_sal.

```sql
DROP PROCEDURE new_sal ;
```

--------------------------------

### Fetch and getValue methods example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/Throws_8.html

Illustrates the fetch method and the getValue method in a loop. The loop continues until the cursor is not found, retrieving values for cust_number and cust_name.

```unknown
for (;;) { cust_cursor.Fetch (); if (cust_cursor.Found ()) { cust_cursor.getValue (1, cust_number); cust_cursor.getValue (2, cust_name) ; } else break; }
```

--------------------------------

### CREATE SYNONYM example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/CREATE-SYNONYM.html

Example demonstrating the CREATE SYNONYM statement, creating a private synonym 'customer' for 'smith.customer' and a public synonym 'public_suppliers' for 'smith.suppliers'.

```sql
CREATE SYNONYM customer FOR smith.customer ;
CREATE PUBLIC SYNONYM public_suppliers FOR smith.suppliers ;
```

--------------------------------

### SHOW ALL PARTITION TABLE

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-PARTITION.html

Returns a list of partitioned tables.

```sql
SHOW ALL PARTITION TABLE
```

--------------------------------

### Drop a user example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DROP-USER.html

Example of dropping a user named 'Jasper' with DBA privileges.

```sql
DROP USER 'Jasper' ;
```

--------------------------------

### SHOW ALL PARTITION FULL

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-PARTITION.html

Returns all information about all partitions defined in the database.

```sql
SHOW ALL PARTITION FULL;
```

--------------------------------

### SHOW ALL PARTITION basic

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-PARTITION.html

Returns basic information about all partitions in the database.

```sql
SHOW ALL PARTITION
```

--------------------------------

### INITCAP usage example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/INITCAP.html

Example showing how to use the INITCAP function in a SELECT statement to capitalize the last_name column from the customer table.

```sql
SELECT INITCAP (last_name) FROM customer ;
```

--------------------------------

### DROP GROUP example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DROP-GROUP.html

Example that drops the group named carz_group from a multi-tenant database.

```sql
DROP GROUP carz_group;
```

--------------------------------

### SQLCursor open method example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/Throws_13.html

Illustrates the open method of SQLCursor. The code creates a new SQLCursor with a SELECT query and then opens it.

```java
SQLCursor empcursor = new SQLCursor ( "SELECT name, sal FROM emp " ) ; empcursor.Open () ;
```

--------------------------------

### Create table and indexes for subpartitioning

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/Enabling-a-table-for-partitioning.html

Creates table Pub.sub_customer_list and its indexes, including local composite indexes and a global index, in preparation for subpartitioning.

```sql
CREATE TABLE Pub.sub_customer_list ( Custid int, Custname varchar(50), Zipcode int ) AREA "TP area"; CREATE INDEX sub_customer_list_list_idxlc1 ON pub.sub_customer_list_list (Zipcode, Custid, Custname) AREA "TP idx area"; CREATE INDEX sub_customer_list_list_idxlc2 ON pub.sub_customer_list_list (Zipcode, Custid) AREA "TP idx area"; CREATE INDEX sub_customer_list_list_idxg1 ON pub.sub_customer_list (Custid) AREA "TP idx area";
```

--------------------------------

### LOCATE(string_exp1, string_exp2[, start])

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/String-functions.html

Returns the starting position of the first occurrence of `string_exp1` within `string_exp2`. If `start` is not specified, the search begins at the first character position. If `string_exp1` is not found, returns 0.

```APIDOC
## LOCATE(string_exp1, string_exp2[, start])

### Description
Returns the starting position of the first occurrence of `string_exp1` within `string_exp2`. If `start` is not specified, the search begins with the first character position in `string_exp2`. If `start` is specified, the search begins with the character position indicated by the value of `start`. The first character position in `string_exp2` is indicated by the value 1. If `string_exp1` is not found, 0 is returned.

### Function Signature
`LOCATE(string_exp1, string_exp2[, start])`

### Parameters
- **string_exp1** (string) - Required - The string to search for.
- **string_exp2** (string) - Required - The string to search within.
- **start** (integer) - Optional - The character position to begin the search. Must be a literal numeric value or result of another scalar function with underlying data type `SQL_TINYINT`, `SQL_SMALLINT`, or `SQL_INTEGER`.

### Returns
Integer - The starting position of the first occurrence, or 0 if not found.
```

--------------------------------

### REVOKE AUDIT_ADMIN Example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/REVOKE.html

Example revoking the audit administration privilege from user bsmith with RESTRICT.

```sql
REVOKE AUDIT_ADMIN FROM bsmith RESTRICT;
```

--------------------------------

### QUARTER function example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/QUARTER.html

Example query using QUARTER to select orders in the third quarter.

```sql
SELECT * FROM orders WHERE QUARTER (order_date) = 3 ;
```

--------------------------------

### CREATE SUPER-TENANT Syntax

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/CREATE-SUPER-TENANT.html

Shows the syntax for creating a super-tenant. The super_tenant_name must be unique within the database's set of tenants. The optional PRO_DESCRIPTION value allows an ABL description as an arbitrary character string.

```sql
CREATE SUPER_TENANT super_tenant_name[ PRO_DESCRIPTION value ];
```

--------------------------------

### SHOW ALL PARTITION FOR TABLE Pub.Customer FULL COLUMN SIZE 3

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/SHOW-PARTITION.html

Returns all information about all partitions on Pub.Customer, limiting column values to not more than 30 characters.

```sql
SHOW ALL PARTITION FOR TABLE Pub.Customer FULL COLUMN SIZE 3;
```

--------------------------------

### DROP TRIGGER Example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DROP-TRIGGER.html

Example of the DROP TRIGGER statement that drops the trigger named sal_check.

```sql
DROP TRIGGER sal_check ;
```

--------------------------------

### Example: Alter table to be partitioned without schema definition

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/Altering-partitioned-tables-without-partition-schema-definition.html

Creates a table Order with indexes and alters it to be partitioned using index order_idx1. Executing the ALTER TABLE statement marks the Order table as partitioned without a partition schema definition and marks order_idx1 as a local index.

```sql
CREATE TABLE Order ( orderid integer, Item varchar (50), Order_date date, Country varchar (50), State varchar (50), City varchar (50)) AREA "area_type2"; CREATE INDEX order_idx1 ON Order (order_date) AREA "area_type2"; CREATE INDEX order_idx2 ON Order (Item) AREA "area_type2"; ALTER TABLE Order SET PARTITION USING INDEX order_idx1;
```

--------------------------------

### DROP VIEW example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DROP-VIEW.html

Example of the DROP VIEW statement that deletes the view named 'newcustomers'.

```sql
DROP VIEW newcustomers ;
```

--------------------------------

### INSERT Timestamp Literal Example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/Timestamp-literals.html

Example of inserting a timestamp literal into a column using the INSERT statement.

```sql
INSERT INTO DTEST VALUES ( { ts '1956-05-07 10:41:37'} ) ;
```

--------------------------------

### Example: INSERT with customer_sequence.NEXTVAL

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/NEXTVAL.html

Inserts a new customer into pub.customer, using customer_sequence.NEXTVAL to generate the customer ID. The sequence increments and returns the new value.

```sql
INSERT INTO pub.customer VALUES (customer_sequence.NEXTVAL,'USA','BackCountry Equipment','Sugar Hill Road','12A','Franconia','NH','03242','Dan Egan','603-762-2121','Kirsten Ulmner', 10000.00, 500.00,'net 10', 0,'contact monthly');
```

--------------------------------

### Example query using DB_NAME

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/DB_NAME.html

Example SQL query that selects the database name from table T2, followed by the displayed output showing the result 'demo'.

```sql
SELECT DB_NAME() FROM T2;
```

```text
DB_NAME
-------
demo
1 record selected
```
Tokens:
Raw