Skip to main contentSkip to search
Powered by Zoomin Software. For more details please contactZoomin
Progress DocumentationProgress Documentation
Progress Documentation
  • Home
  • Home
  • EnglishČeštinaDeutsch (Germany)Español (Spain)FrançaisItaliano (Italy)Português (Brasil)日本語Русский (Russia)中文 (简体) (China)中文 (繁體, 台灣) (Taiwan)ar-AR
  • Login

OpenEdge SQL Reference

OpenEdge SQL language elements

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
Table of Contents
  • SQL Reference
    • OpenEdge SQL statements
      • ALTER DATABASE SET PRO_ENABLE_LARGE_KEYS
      • ALTER DATABASE SET PRO_ENABLE_64BIT_SEQUENCES
      • ALTER GROUP
        • Authorization
        • Related Statements
      • ALTER SEQUENCE
        • Authorization
        • Related Statement
      • ALTER TABLE
        • Defining LOB columns
        • ALTER TABLE RENAME COLUMN
        • ALTER TABLE RENAME INDEX
        • ALTER TABLE DROP COLUMN
        • Converting a table to a multi-tenant table
        • Repairing a CRC mismatch error
        • Migrating data from existing unpartitioned tables to partitioned tables
          • Enabling a table for partitioning
          • Marking partitions as split targets
          • Moving the physical data from the composite initial partition to its respective partitions
          • Examples
        • ALTER TABLE syntax to add or drop partitions
        • Authorization
        • Related statements
        • Altering partitioned tables without partition schema definition
      • ALTER TENANT
        • Authorization
        • Related Statement
      • ALTER USER
        • Authorization
        • Related statements
      • AUDIT INSERT
      • AUDIT SET
        • Authorization
      • CALL
        • Authorization
        • Related statements
      • Column constraints
      • COMMIT
        • Related Statement
      • CONNECT AS CATALOG
        • Authorization
        • SQL Compliance
        • Related statements
      • CREATE DOMAIN
        • Authorization
        • Related Statement
      • CREATE GROUP
        • Authorization
        • Related Statement
      • CREATE INDEX
        • Creating indexes for partitioned tables
        • Authorization
        • Related statements
      • CREATE PROCEDURE
        • Authorization
        • Related statements
      • CREATE SEQUENCE
        • Authorization
        • Related Statements
      • CREATE SUPER-TENANT
        • Authorization
        • Related Statement
      • CREATE SYNONYM
        • Authorization
        • Related Statement
      • CREATE TABLE
        • Authorization
        • Related Statement
      • CREATE TENANT
        • Authorization
        • Related Statement
      • CREATE TRIGGER
        • Authorization
        • Related statement
      • CREATE USER
        • Authorization
        • Related statements
      • CREATE VIEW
        • Authorization
        • Related statements
      • DELETE
        • Authorization
        • Related statements
      • DISCONNECT CATALOG
        • Authorization
        • SQL Compliance
        • Related statements
      • DROP DOMAIN
        • Authorization
        • Related Statement
      • DROP GROUP
        • Authorization
        • Related statements
      • DROP INDEX
        • Authorization
        • Related statement
      • DROP PROCEDURE
        • Authorization
        • Related statement
      • DROP SEQUENCE
        • Authorization
        • Related Statement
      • DROP SYNONYM
        • Authorization
        • Related statement
      • DROP TABLE
        • Authorization
        • Related statement
      • DROP TENANT
        • Authorization
        • Related Statement
      • DROP TRIGGER
        • Authorization
        • Related statement
      • DROP USER
        • Authorization
        • Related statements
      • DROP VIEW
        • Authorization
        • Related statement
      • GRANT
        • Authorization
        • Related statement
      • INSERT
        • Authorization
        • Related statement
      • LOCK TABLE
        • Authorization
        • Related statements
      • REVOKE
        • Syntax to revoke privileges on specific tables and views
        • Authorization
        • Related statement
      • ROLLBACK
        • Authorization
        • Related statements
      • SELECT
        • COLUMN_LIST clause
        • TOP clause
        • FROM clause
        • WHERE clause
        • GROUP BY clause
          • Grouping by column
          • Grouping by alias
          • Grouping by expression
        • HAVING clause
        • ORDER BY clause
        • OFFSET and FETCH clauses
        • WITH clause
        • FOR UPDATE clause
        • Authorization
        • Related Statements
      • SET CATALOG
        • Authorization
        • SQL Compliance
        • Related statements
      • SET DATABASE DEFAULT AREA
      • SET PRO_CONNECT LOG
      • SET PRO_CONNECT QUERY_TIMEOUT
      • SET PRO_SERVER QUERY_TIMEOUT
      • SET PRO_SERVER LOG
      • SET ROWCOUNT
        • Related Statement
      • SET SCHEMA
        • Authorization
      • SET TRANSACTION ISOLATION LEVEL
        • Authorization
        • Related statements
      • SHOW CATALOGS
        • Authorization
        • SQL Compliance
        • Related statements
      • SHOW DATABASE DEFAULT AREA
      • SHOW GROUP
        • Authorization
        • Related Statement
      • SHOW PARTITION
        • Authorization
      • SHOW ROWCOUNT
        • Related Statement
      • SHOW TENANT
        • Authorization
        • Related Statement
      • SHOW ENCRYPT ON
        • Authorization
        • SQL Compliance
        • Related statements
      • Table constraints
      • UPDATE
        • Authorization
        • Related statements
      • UPDATE STATISTICS
        • Authorization
    • OpenEdge SQL functions
      • About OpenEdge SQL functions
        • Aggregate functions
        • Scalar functions
      • ABS
      • ACOS
      • ADD_MONTHS
      • ASCII
      • ASIN
      • ATAN
      • ATAN2
      • AVG
      • CASE
      • CAST
      • CDC_get_changed_columns
      • CDC_is_column_changed
      • CEILING
      • CHAR
      • CHR
      • COALESCE
      • CONCAT
      • CONVERT (ODBC compatible)
      • CONVERT (Progress extension)
      • COS
      • COUNT
      • CURDATE
      • CURTIME
      • CURRVAL
      • DATABASE
      • DAYNAME
      • DAYOFMONTH
      • DAYOFWEEK
      • DAYOFYEAR
      • DB_NAME
      • DECODE
      • DEGREES
      • EXP
      • FLOOR
      • GREATEST
      • HOUR
      • IFNULL
      • INITCAP
      • INSERT
      • INSTR
      • ISOWEEKDAY
      • ISOWEEK
      • ISOYEAR
      • LAST_DAY
      • LCASE
      • LEAST
      • LEFT
      • LENGTH
      • LOCATE
      • LOG10
      • LOWER
      • LPAD
      • LTRIM
      • MAX
      • MIN
      • MINUTE
      • MOD
      • MONTH
      • MONTHNAME
      • MONTHS_BETWEEN
      • NEXT_DAY
      • NEXTVAL
      • NOW
      • NULLIF
      • NVL
      • PI
      • POWER
      • PREFIX
      • PRO_ARR_DESCAPE function
      • PRO_ARR_ESCAPE function
      • PRO_ELEMENT function
      • QUARTER
      • RADIANS
      • RAND
      • REPEAT
      • REPLACE
      • RIGHT
      • ROUND
      • ROWID
      • RPAD
      • RTRIM
      • SECOND
      • SIGN
      • SIN
      • SQRT
      • SUBSTR
      • SUBSTRING (ODBC compatible)
      • SUFFIX
      • SUM
      • SYSDATE
      • SYSTIME
      • SYSTIMESTAMP
      • TAN
      • TO_CHAR
      • TO_DATE
      • TO_NUMBER
      • TO_TIME
      • TO_TIMESTAMP
      • TRANSLATE
      • UCASE
      • UPPER
      • USER
      • WEEK
      • YEAR
    • OpenEdge SQL reserved words
    • OpenEdge SQL error messages
      • Overview
      • Error codes, SQLSTATE values, and messages
    • OpenEdge SQL system limits
    • OpenEdge SQL system catalog tables
      • Overview of system catalog tables
      • SYSTABLES
      • SYSCOLUMNS
      • SYSINDEXES
      • SYSBIGINTSTAT
      • SYSCALCTABLE
      • SYSCHARSTAT
      • SYSNCHARSTAT
      • SYSCHKCONSTR_NAME_MAP
      • SYSCOLAUTH
      • SYSCOLSTAT
      • SYSCOLUMNS_FULL
      • SYSDATATYPES
      • SYSDATESTAT
      • SYSDBAUTH
      • SYSFLOATSTAT
      • SYSIDXSTAT
      • SYSINTSTAT
      • SYSNUMSTAT
      • SYSPROCBIN
      • SYSPROCCOLUMNS
      • SYSPROCEDURES
      • SYSPROCTEXT
      • SYSPROCAUTH
      • SYSREALSTAT
      • SYSROLES
      • SYSSEQAUTH
      • SYSSEQUENCES
      • SYSSCHEMAS
      • SYSSYNONYMS
      • SYSTABAUTH
      • SYSTABLES_FULL
      • SYSTBLSTAT
      • SYSTIMESTAT
      • SYSTINYINTSTAT
      • SYSTRIGCOLS
      • SYSTRIGGER
      • SYSTSSTAT
      • SYSSQL_PROPERTIES
      • SYSTSTZSTAT
      • SYSNVARCHARSTAT
      • SYSVARCHARSTAT
      • SYSVIEWS
      • SYS_CHKCOL_USAGE
      • SYS_CHK_CONSTRS
      • SYS_KEYCOL_USAGE
      • SYS_REF_CONSTRS
      • SYS_TBL_CONSTRS
    • OpenEdge SQL language elements
      • OpenEdge SQL identifiers
        • Conventional identifiers
        • Delimited identifiers
      • Number formats
      • Date-time formats
      • Date formats
      • Time formats
      • Data types
        • Character data types
          • Maximum length for VARCHAR
          • National Language Support (NLS)
          • Concatenation operator
        • Exact numeric data types
        • Approximate numeric data types
        • Date-time data types
        • Bit string data types
          • Maximum length for VARBINARY
          • BLOB limitations
          • Language support for BLOB
          • Utility support for BLOB
          • CLOB limitations
          • Utility support for LVARCHAR (CLOB)
        • Array data types
          • ARRAY element reference
          • Default value for ARRAY columns
          • Assignment
          • Comparison
        • Literals
          • Numeric literals
          • Character-string literals
          • Date-time literals
            • Date literals
            • Time literals
            • Timestamp literals
        • Relational operators
          • Basic Predicate
          • Quantified Predicate
            • BETWEEN Predicate
            • NULL Predicate
            • LIKE Predicate
            • EXISTS Predicate
            • IN Predicate
            • OUTER JOIN Predicate
          • Numeric arithmetic expressions
          • Date arithmetic expressions
    • Data type compatibility
      • Supported ABL data types and corresponding OpenEdge SQL data types
    • OpenEdge SQL elements and statements in Backus Naur Form
      • Data Type
        • Character data type
        • Exact numeric data type
        • Approximate numeric data type
        • Date-time data type
        • Bit string data type
      • Expressions syntax in BNF
        • Expression (expr)
          • Numeric arithmetic expression
          • Date arithmetic expression
        • Conditional expression
          • Case expression
          • Searched case expression
          • Simple case expression
      • Literals syntax in BNF
        • Date literal
        • Time literal
        • Timestamp literal
        • Timestamp with time zone literal
      • Query Expressions syntax in BNF
        • Query expression
          • Set operator
          • Query specification
          • Table reference
          • Joined table
          • From clause inner join
          • Where clause inner join
          • From clause outer join
          • Where clause outer join
      • Search conditions syntax in BNF
        • Search condition
          • Predicate
          • Relational operator
          • Basic predicate
          • Quantified predicate
          • Between predicate
          • Null predicate
          • Like predicate
          • Exists predicate
          • In predicate
          • Outer join predicate
      • Statements, DDL and DML syntax in BNF
        • ALTER USER
        • CALL
        • COMMIT
        • CREATE INDEX
        • CREATE PROCEDURE
          • Parameter Declaration
        • CREATE SYNONYM
        • CREATE TABLE
          • Column Definition
          • Column Constraint
          • Table Constraint
        • CREATE TRIGGER
        • CREATE USER
        • CREATE VIEW
        • DELETE
        • DROP INDEX
        • DROP PROCEDURE
        • DROP SYNONYM
        • DROP TABLE
        • DROP TRIGGER
        • DROP USER
        • DROP VIEW
        • GRANT RESOURCE, DBA
        • GRANT PRIVILEGE
          • PRIVILEGE Syntax
        • INSERT
        • LOCK TABLE
        • REVOKE RESOURCE, DBA
        • REVOKE PRIVILEGE
          • PRIVILEGE Syntax
        • ROLLBACK
        • SELECT
        • SET SCHEMA
        • UPDATE
          • Assignment clause
        • UPDATE STATISTICS
    • Compliance with industry standards
      • Scalar functions
      • SQL-92 DDL and DML statements
    • Syntax for ABL attributes
      • OpenEdge SQL keywords for ABL table attributes
    • Impact of Dynamic Data Masking on SQL constructs
      • SELECT
        • WHERE
        • GROUP BY
        • HAVING
        • ORDER BY
        • DISTINCT
      • INSERT
      • UPDATE
      • DELETE
      • ALTER
      • JOINS
      • Aggregate functions
      • Scalar functions
      • Views
      • Triggers
      • Stored procedures
      • User-defined functions
      • SQLDUMP and SQLLOAD utility
      • Authorized data truncation
      • Autonomous schema update
      • DDM support for SET operators
  • JDBC Reference
    • Java class reference
      • Java classes and methods
        • setParam
        • makeNULL
        • execute
        • rowCount
        • open
        • close
        • fetch
        • found
        • wasNULL
        • getValue
        • set
        • insert
        • getDiagnostics
        • log
        • err
      • DhSQLException
        • Constructors
        • Parameters
        • DhSQLException.getDiagnostics
          • Format
          • Returns
          • Parameter
          • Throws
        • DhSQLException.err
      • DhSQLResultSet
        • Constructors
        • Parameters
        • Throws
        • DhSQLResultSet.insert
          • Format
          • Returns
          • Parameters
          • Throws
        • DhSQLResultSet.makeNULL
          • Format
          • Returns
          • Parameter
          • Throws
        • DhSQLResultSet.set
          • Format
          • Returns
          • Parameters
          • Throws
      • SQLCursor
        • Constructors
        • Parameters
        • Throws
        • SQLCursor.close
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.fetch
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.found
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.getParam
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.getValue
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.makeNULL
          • Format
          • Returns
          • Parameter
          • Throws
        • SQLCursor.open
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.registerOutParam
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.rowCount
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.setParam
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLCursor.wasNULL
          • Format
          • Returns
          • Parameter
          • Throws
      • SQLIStatement
        • Constructors
        • Parameter
        • Throws
        • SQLIStatement.execute
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLIStatement.makeNULL
          • Format
          • Returns
          • Parameter
          • Throws
        • SQLIStatement.rowCount
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLIStatement.setParam
          • Format
          • Returns
          • Parameters
          • Throws
      • SQLPStatement
        • Constructors
        • Parameter
        • Throws
        • SQLPStatement.execute
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLPStatement.makeNULL
          • Format
          • Returns
          • Parameter
          • Throws
        • SQLPStatement.rowCount
          • Format
          • Returns
          • Parameters
          • Throws
        • SQLPStatement.setParam
          • Format
          • Returns
          • Parameters
          • Throws
    • JDBC conformance notes
      • Supported data types
      • Return values for DatabaseMetaData
  • ODBC Reference
    • OpenEdge SQL and ODBC data types
    • SQLGetInfo
    • ODBC scalar functions
      • Scalar functions
        • String functions
        • Numeric functions
        • Date and time functions
      • System functions
  • ESQL Reference
    • Embedded SQL
      • ESQL elements and statements
        • BEGIN-END DECLARE SECTION
          • Authorization
          • Related statement
        • CLOSE
          • Authorization
          • Related statements
        • CONNECT
          • Authorization
          • Related statements
        • DECLARE CURSOR
          • Authorization
          • Related statements
        • DESCRIBE
        • DESCRIBE BIND VARIABLES
          • Authorization
          • Related statements
        • DESCRIBE SELECT LIST
          • Authorization
          • Related statements
        • DISCONNECT
          • Authorization
          • Related statements
        • EXEC SQL delimiter
          • Authorization
        • EXECUTE
          • Related statements
        • EXECUTE IMMEDIATE
          • Related statement
        • FETCH
          • Authorization
          • Related statements
        • GET DIAGNOSTICS
          • Related statements
        • GET DIAGNOSTICS EXCEPTION
          • Related statements
        • OPEN
          • Authorization
          • Related statements
        • PREPARE
          • Authorization
          • Related statements
        • SET CONNECTION
          • Authorization
          • Related statements
        • WHENEVER
          • Authorization
          • Related statements
      • ESQL elements and statements in Backus Naur Form (BNF)
        • BEGIN-END DECLARE SECTION
        • Host Language Type
        • CLOSE
        • CONNECT
        • CONNECT STRING
        • DECLARE CURSOR
        • DESCRIBE BIND VARIABLES
        • DESCRIBE SELECT LIST
        • DISCONNECT
        • EXEC SQL
        • EXECUTE
        • EXECUTE IMMEDIATE
        • FETCH
        • GET DIAGNOSTICS
        • Header Info Item
        • GET DIAGNOSTICS EXCEPTION
        • Detail Info Item
        • OPEN
        • PREPARE
        • SET CONNECTION
        • SET TRANSACTION ISOLATION LEVEL
        • ISOLATION LEVEL NAME
        • WHENEVER
      • Compliance with industry standards
Table of Contents

OpenEdge SQL language elements

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
  • Last Updated: January 17, 2024
  • 1 minute read
    • OpenEdge
    • Version 12.8
    • Documentation

This topic describes Standard SQL language elements that are common to OpenEdge® SQL.

Contents
  • OpenEdge SQL identifiers
  • Number formats
  • Date-time formats
  • Date formats
  • Time formats
  • Data types
TitleResults for “How to create a CRG?”Also Available inAlert