Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Documentation bug in example for the window_clause

Philipp SalvisbergDec 28 2023 — edited Dec 28 2023

The Example in https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__GUID-1B520407-DD06-4568-8E64-25F90F4C0B59 looks like this:

The example uses single quotes for the column alias. This does not work and throws the following error message in SQLcl 23.3:

Error starting at line : 1 in command -
SELECT
      ename, mgr,
      FIRST_VALUE(sal) OVER w AS 'first',
      LAST_VALUE(sal) OVER w AS 'last',
      NTH_VALUE(sal, 2) OVER w AS 'second',
      NTH_VALUE(sal, 4) OVER w AS 'fourth'
   FROM emp
   WINDOW w AS (PARTITION BY deptno ORDER BY sal ROWS UNBOUNDED PRECEDING)
Error at Command Line : 3 Column : 34
Error report -
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    In a SELECT or REVOKE statement, the keyword FROM was
           either missing, misplaced, or misspelled. The keyword FROM
           must follow the last selected item in a SELECT statement or
           the privileges in a REVOKE statement.
*Action:   Correct the syntax. Insert the keyword FROM where
           appropriate. The SELECT list itself also may be in error. If
           quotation marks were used in an alias, check that double
           quotation marks enclose the alias. Also, check to see if a
           reserved word was used as an alias.

More Details :
https://docs.oracle.com/error-help/db/ora-00923/

The column alias should use double quotes or no quotes at all. Like this:

SELECT
      ename, mgr,
      FIRST_VALUE(sal) OVER w AS first,
      LAST_VALUE(sal) OVER w AS last,
      NTH_VALUE(sal, 2) OVER w AS second,
      NTH_VALUE(sal, 4) OVER w AS fourth
   FROM emp
   WINDOW w AS (PARTITION BY deptno ORDER BY sal ROWS UNBOUNDED PRECEDING);

The documentation should be amended accordingly.

This post has been answered by Chris Saxon-Oracle on Jan 3 2024
Jump to Answer
Comments
Post Details
Added on Dec 28 2023
1 comment
179 views