You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2014/07/09 14:39:05 UTC
[jira] [Created] (DERBY-6652) Compilation error using a view as the
source data set for a MERGE statement
Rick Hillegas created DERBY-6652:
------------------------------------
Summary: Compilation error using a view as the source data set for a MERGE statement
Key: DERBY-6652
URL: https://issues.apache.org/jira/browse/DERBY-6652
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.11.0.0
Reporter: Rick Hillegas
Mamta found the following problem when using a view as the source data set of a MERGE statement:
{noformat}
connect 'jdbc:derby:memory:db;create=true';
CREATE TABLE employee (
employee_id int,
first_name VARCHAR(20),
last_name VARCHAR(20),
dept_no int,
salary int);
create view v1employee as select * from employee;
CREATE TABLE bonuses (
employee_id int, bonus int DEFAULT 100);
create view v2bonuses as select * from bonuses;
-- compiles without the view
MERGE INTO bonuses B
USING employee E
ON B.employee_id = E.employee_id
WHEN MATCHED AND E.dept_no=20 THEN
UPDATE SET B.bonus = E.salary * 0.1
WHEN NOT MATCHED AND dept_no=20 THEN
INSERT (employee_id, bonus)
VALUES (E.employee_id, E.salary * 0.05);
-- compilation error with the view
MERGE INTO bonuses B
USING v1employee E
ON B.employee_id = E.employee_id
WHEN MATCHED AND E.dept_no=20 THEN
UPDATE SET B.bonus = E.salary * 0.1
WHEN NOT MATCHED AND dept_no=20 THEN
INSERT (employee_id, bonus)
VALUES (E.employee_id, E.salary * 0.05);
{noformat}
--
This message was sent by Atlassian JIRA
(v6.2#6252)