[
https://issues.apache.org/jira/browse/DERBY-4803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12913521#action_12913521
]
Rick Hillegas commented on DERBY-4803:
--------------------------------------
Tests passed cleanly for me. Committed derby-4803-01-aa-simpleInsertSelect.diff
at subversion revision 999908.
Hi DK,
Your script now passes. I had to edit your script because it was trying to
insert integers into varchar columns. Those problems must have been masked by
this bug. Here is the modified script which now runs:
connect 'jdbc:derby:memory:db;create=true';
CREATE SEQUENCE CART_TXN_ID_SEQ START WITH 1;
CREATE TABLE TABLE_1
(
DOCNO VARCHAR(60) NOT NULL
, OTHER_DOCNO VARCHAR(60)
, CAN VARCHAR(7) NOT NULL
, ICD VARCHAR(6)
, FY VARCHAR(4)
, SGL_DEBIT_AMT DECIMAL(12, 2)
, FACTS_TP_CODE VARCHAR(10)
, AUTHORITY_DOCNO VARCHAR(60)
, ACTION_CODE VARCHAR(20)
, CART_TXN_ID INT
, CART_ID VARCHAR(50)
, LAST_UPDATED_BY_USER_ID VARCHAR(20)
, CREATED_DATE TIMESTAMP
, CONSTRAINT TABLE_1 PRIMARY KEY
(
CART_TXN_ID
)
);
CREATE TABLE TABLE_2
(
PROJECT_BUDGET CHAR(1)
, ACS_CAN CHAR(7)
, DIRECT_REIMB_FLAG CHAR(1)
, INSTITUTE CHAR(25)
, PROJECT_NBR CHAR(6)
, CURRENT_IND CHAR(1)
);
insert into TABLE_2(project_budget, acs_can, direct_reimb_flag, institute,
project_nbr, current_ind)
values('P', '1234567', 'R', 'XYZ', '123456', 'C');
INSERT INTO
TABLE_1 (
cart_id,
can,
docno,
fy,
icd,
other_docno,
facts_tp_code,
authority_docno,
SGL_DEBIT_AMT,
action_code,
cart_txn_id,
last_updated_by_user_id,
created_date
) SELECT
'Abc',
'1234567',
'Y21',
'2010',
TRIM(acs.institute),
'Y3123456',
'123',
'Y3123456',
100,
'Action',
NEXT VALUE FOR CART_TXN_ID_SEQ,
'Qwerty',
CURRENT_TIMESTAMP
FROM
table_2 acs
WHERE
acs.acs_can = '1234567';
select * from table_1;
> Sequences do not work in INSERT/SELECT
> --------------------------------------
>
> Key: DERBY-4803
> URL: https://issues.apache.org/jira/browse/DERBY-4803
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.6.1.0
> Environment: Mac OS X, Derby Network Server 10.6.1.0
> Reporter: DK
> Assignee: Rick Hillegas
> Fix For: 10.7.0.0
>
> Attachments: derby-4803-01-aa-simpleInsertSelect.diff,
> DerbyTestCase.sql
>
>
> Using sequence in SELECT works fine whereas the same SELECT query used in
> INSERT/SELECT results in "The statement references the following sequence
> more than once" error. This happens even though the SELECT in question
> returns exactly 1 row of data.
> The Reference Manual states 1. " NEXT VALUE FOR expression may occur in the
> following places: SELECT statement: As part of the expression defining a
> returned column in a SELECT list" and 2. " NEXT VALUE expression may not
> appear in any of these situations: CASE expression, WHERE clause,
> ORDER BY clause, Aggregate expression, ROW_NUMBER function, DISTINCT select
> list".
> Nowhere a restriction on INSERT/SELECT is mentioned. Additionally, other
> databases (i.e. Oracle) support use of sequences in INSERT/SELECT.
> Therefore, I consider it a bug.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
