### GET DIAGNOSTICS Example

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

Demonstrates how to extract header information about the last SQL statement executed and assign it to host variables.

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

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

### SQL Query Example

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

This example demonstrates a basic SQL query.

```sql
SELECT C1 FROM T2;
```

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

### Example of INSERT Function Usage

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

Illustrates how to use the INSERT function to modify a string. This example deletes 4 characters starting from the 2nd position in 'Goldman' and inserts 'xx'.

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

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

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

### CREATE TABLE for Range Partitioning Example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/ALTER-TABLE-syntax-to-add-or-drop-partitions.html

Example of creating a table partitioned by RANGE on the 'custid' column. This sets up the initial partitions before using ALTER TABLE.

```sql
CREATE TABLE Pub.tpCustRange
** (
  Custid int,
  Custname varchar **(**50**)**,
  Salary int
** )
 PARTITION BY RANGE custid
 USING TABLE AREA "Customer Table Area"
 **(
 PARTITION partn20k VALUES <= **(**20000**)**,
 PARTITION partn50k VALUES <= **(**50000**)**,
 PARTITION partn80k VALUES <= **(**80000**)**,**
 );
```

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

### CREATE TABLE for Subpartitioning Example

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/ALTER-TABLE-syntax-to-add-or-drop-partitions.html

Example of creating a table with LIST partitioning on 'a', SUBPARTITIONING by LIST on 'b', and further SUBPARTITIONING by RANGE on 'c'. This demonstrates complex partitioning structures.

```sql
CREATE TABLE Pub.tpsub
** (
   **a int,
  b int,
  c int,
  d int
 **)
  **PARTITION BY LIST a
  SUBPARTITION BY LIST b
  SUBPARTITION BY RANGE c	
  USING TABLE AREA "Tenant 1 table Area"
  ( ** 
   PARTITION  sub_a values <= (1,1,100),
   PARTITION  sub_b values <= (2,2,200),
   PARTITION  sub_c values <= (3,3,100),
   PARTITION  sub_d values <= (3,3,200),
   PARTITION  sub_e values <= (3,3,300)
**  );
```

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

### CREATE GROUP Example

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

Example of creating a group for the 'pub.customer' table, allocating space for the table, index, and LOB, and adding a description.

```sql
CREATE GROUP carz_second FOR TABLE pub.customer
		   **[** TABLE AREA "CUSTTABAREA" **] 
**		   **[** INDEX AREA "CUSTIDAREA" **] 
**		   **[** LOB AREA "CUSTTLOBAREA" **]**
    	   **[** PRO_DESCRIPTION "Second Group for CARZ" **]
```

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

### Example of LOCATE Function Usage

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

Demonstrates using the LOCATE function with string literals and a starting position. The search is case-sensitive.

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

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

### Create a Partitioned Table with RANGE Partitioning and Global Index

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

This example demonstrates RANGE partitioning by date, resulting in a global index for constraints due to the unique constraint not being on the partition key.

```SQL
CREATE TABLE Pub.tpcustomer **
 (**
  "cust_num" int,
   Name VARCHAR (60) UNIQUE,
   Address VARCHAR (160),
   Zip VARCHAR (160),
   "SalesRep" VARCHAR (160),
   "tp_date" date,
   "r_value" int
 **)
 PARTITION BY RANGE "tp_date" 
 USING TABLE AREA "Tenant 1_table_area"
  **(
  PARTITION tpcustomer_p1 VALUES <= ( '12/31/2011' ),
  PARTITION tpcustomer_p2 VALUES <= ( '12/31/2012' ),
  PARTITION tpcustomer_p3 VALUES <= ( '12/31/2013'
  ****)**;
```

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

### PRO_ELEMENT with NULL element returning '?'

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

If the start and end positions are the same, and the referenced array element is NULL, PRO_ELEMENT returns '?'. This example demonstrates this behavior.

```sql
PRO_ELEMENT(sales_history, 2, 2)
```

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

### CREATE TABLE Partitioned by LIST

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

This example demonstrates partitioning a table using the LIST method. The specific implementation details for LIST partitioning are not shown in this snippet.

```sql
CREATE TABLE Pub.tpcustomer 
(
  custid int,
  custname VARCHAR (50),
  join_date date,
  salary int
)
   PARTITION BY LIST custid USING TABLE AREA "area_pt" 
   (
     PARTITION p1 VALUES in (1000, 2000) USING TABLE AREA "area_p1",
     PARTITION p2 VALUES in (3000, 4000) USING TABLE AREA "area_p2",
     PARTITION p3 VALUES in (5000, 6000) USING TABLE AREA "area_plast"
   );
```

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

### ADD_MONTHS Example

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

Illustrates how to use the ADD_MONTHS function to filter records where the start date plus six months is after the current system date.

```sql
SELECT *
    FROM  customer
    WHERE ADD_MONTHS (start_date, 6) > SYSDATE ;
```

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

### Use SUFFIX to extract substring from the beginning

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

This example shows how to use the SUFFIX function to extract a substring starting from the first character of column C1, using the character in column C2 as the delimiter. This is useful when the delimiter's position is variable and specified in another column.

```SQL
SELECT C1, C2, SUFFIX(C1, 1, C2) FROM T1;
 C1      C2 SUFFIX(C1,1,C
--      -- ------------- 
test.pref  . pref     
pref.test  s  t      
 2 records selected
```

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

### Create a Table with Read-Only Partitions

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

This example illustrates creating a table with read-only partitions using LIST and SUBPARTITION clauses. Some partitions are explicitly marked as READ_ONLY.

```SQL
CREATE TABLE Pub.tpcustomer 
 **(**
   a int,
   b int,
   c int,
   d int 
 **)
 PARTITION BY LIST a
 SUBPARTITION BY LIST b
 SUBPARTITION BY LIST c
 USING TABLE AREA "Tenant 1_table_area" 
  **(
    PARTITION sub1_a VALUES IN ( 10, 11, 12) READ_ONLY,
    PARTITION sub1_b VALUES IN ( 20, 21, 22),
    PARTITION sub1_c VALUES IN ( 30, 31, 32) READ_ONLY 
  **);
```

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

### Create a Table with LIST-LIST-LIST Subpartitioning

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

This example shows how to create a table with multiple levels of LIST partitioning (subpartitioning) on Country, State, and City.

```SQL
CREATE TABLE tporder_list 
 **(**orderid integer,
  Item varchar(50),
  Order_date date,
  Country varchar(50),
  State varchar(50),
  City varchar(50)**)**
  PARTITION BY LIST (Country)
  SUBPARTITION BY LIST(State)
  SUBPARTITION BY LIST(City)
  USING TABLE AREA "order list table area"
   (**  PARTITION USA_MA_BT VALUES IN ( ‘USA','MA','Boston' ),
     PARTITION USA_NY_NY VALUES IN ( ‘USA','NY','New York' ),
       USING INDEX AREA "Secunderabad index area"
     PARTITION USA_MA_BD VALUES IN ( ‘USA,'MA','Bedford'**)**
```

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

### Use SUFFIX to extract substring after a specific character

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

This example demonstrates using the SUFFIX function to extract a substring from column C1. It starts searching from the 6th character and looks for a '.' to determine the end of the prefix. This is useful for parsing strings where a delimiter marks the end of a relevant section.

```SQL
SELECT C1, C2, SUFFIX(C1, 6, '.') FROM T1;
C1      C2 SUFFIX(C1,6,. 
--      -- ------------- 
test.pref  .       
pref.test  s        
 2 records selected 
```

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

### CONNECT Statement Examples

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

Illustrates how to connect to different databases using the CONNECT statement, including local databases and environment-defined defaults.

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

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

### DROP DOMAIN Example

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

An example demonstrating how to use the DROP DOMAIN command.

```APIDOC
## Example

```sql
DROP DOMAIN jasper;
```
```

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

### Example of DROP VIEW Statement

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

This example demonstrates how to drop a view named 'newcustomers'.

```sql
DROP VIEW newcustomers ;
```

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

### Create Table with List Partitions

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/ALTER-TABLE-syntax-to-add-or-drop-partitions.html

Illustrates the creation of a table with partitions defined by specific list values.

```SQL
CREATE TABLE Pub.tpCustList
 (
  Custid int,
  Custname varchar (50),
  City varchar (50),
  join_date date,
  Salary int
 )
  PARTITION BY LIST city
  USING TABLE AREA "Customer Table Area"
  (
  PARTITION p1 VALUES IN ( 'Atlanta' ),
  PARTITION p2 VALUES IN ( 'Montgomery' ),
  PARTITION p3 VALUES IN ( 'Boston')
);
```

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

### Convert Table to Multi-Tenant with Custom Allocations

Source: https://docs.progress.com/bundle/openedge-sql-reference/page/Converting-a-table-to-a-multi-tenant-table.html

This example demonstrates converting a table to a multi-tenant table while specifying custom storage areas for tables, indexes, and LOBs for specific tenants. It also includes an option for default tenant with no space.

```sql
ALTER TABLE pub.mtcustomer  SET MULTI_TENANT 
FOR TENANT "Consolidated_Freightways" 
               USING TABLE AREA "MMM Data area"
               USING INDEX AREA "MM CF Fin idx area"
FOR TENANT Mega_Media_Networks
               USING LOB AREA "MMM pix area" ;
	FOR TENANT DEFAULT USING NO SPACE;
```

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

### Example of DROP PROCEDURE

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

This example demonstrates how to drop a stored procedure named 'new_sal'.

```sql
DROP PROCEDURE new_sal ;
```

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

### BETWEEN Predicate Example

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

Example of using the BETWEEN predicate to filter results based on a salary range.

```sql
salary BETWEEN 20000.00 AND 100000.00
```

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

### SET CATALOG Example

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

This example sets an auxiliary database connection, identified by 'mydb1', as the default catalog. Ensure the specified catalog is currently connected.

```sql
SET CATALOG mydb1;
```