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)法語Italiano (Italy)Português (Brasil)日本語Русский (Russia)中文 (简体) (China)中文 (繁體, 台灣) (Taiwan)ar-AR
  • Login

OpenEdge SQL Reference

OpenEdge SQL elements and statements in Backus Naur Form

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 elements and statements in Backus Naur Form

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 presents OpenEdge SQL language elements and statements in Backus Naur Form (BNF).

Contents
  • Data Type
  • Expressions syntax in BNF
  • Literals syntax in BNF
  • Query Expressions syntax in BNF
  • Search conditions syntax in BNF
  • Statements, DDL and DML syntax in BNF
TitleResults for “How to create a CRG?”Also Available inAlert