Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://github.com/pmd/pmd/issues/1656 below:

[plsql] ParseException with analytic functions, trim and subqueries · Issue #1656 · pmd/pmd · GitHub

• Pricing

Affects PMD Version: 6.11.0

There are a couple of parse problems:

1. AnalyticClause (OVER)

See https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Analytic-Functions.html

-- Example from: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/AVG.html
SELECT manager_id, last_name, hire_date, salary,
       AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
  INTO some_record
  FROM employees
  ORDER BY manager_id, hire_date, salary;

1. LISTAGG Function

See https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/LISTAGG.html

-- Example from: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/LISTAGG.html
SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  INTO some_record
  FROM employees
  WHERE department_id = 30;

1. SELECT INTO should allow to address a field of a record variable

SELECT the_id
    INTO my_record.the_id
    FROM my_table
    WHERE the_id = '1';

1. Subqueries in SELECTs should be possible

SELECT (SELECT a FROM DUAL) INTO foo FROM DUAL;

1. Update Statement doesn't support RETURNING

UPDATE employees
   SET salary = salary * 1.1
   WHERE department_id = 100
   RETURNING SUM(salary) INTO :bnd1;

1. TRIM function is not supported yet

See https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/TRIM.html

SELECT employee_id,
      TO_CHAR(TRIM(LEADING 0 FROM hire_date))
      FROM employees
      WHERE department_id = 60
      ORDER BY employee_id;

You can’t perform that action at this time.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4