Getting Error While Inserting Multiple Rows

SUBRAHMANYAM · #1 (**permalink**) 05-29-09, 06:43

HI ALL,
I AM GETTING ERROR WHILE INSERTING MULTIPLE ROWS INTO TABLE ,THE ERROR AS "
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.
"
PLEASE DO THE NEEDFUL.

THE SQL STATEMENT AS FOLLOWS..

INSERT INTO E2WDB2D.JOB( JOB_ID
,PARENT_FLAG
,ORDER_ID
,FUNCTION_ID
,CONTRACT_ID
,JOB_DESCRIPTION
,JOB_SOURCE
,BOOKED_DATE
,JOB_TYPE
,JOB_STATUS
,JOB_STATUS_DATE
,JOB_STATUS_REASON
,BILL_TO_DATE_AMOUNT
,DEPOSIT_AMOUNT
,WORK_ORDER_NUMBER
,WORK_ORDER_DESCRIPTION
,CONSTR_REQUIRED_FLAG
,MILESTONE_BILLING_FLAG
,CAPITAL_EXEMPT_CERT_NUM
,CUSTOM_INSTALL_FLAG
,CUSTOMER_JOB_SEQ
,CREATE_PROCESS_DATE
,CREATE_DATE
,CREATE_USERID
,CREATE_CLIENT_USERID
,UPDATE_USERID
,UPDATE_CLIENT_USERID
,RECORD_LIFECYCLE_STATUS
)
SELECT NEXT VALUE FOR E2WDB2D.JOB_ID_SEQ
,'N'
,2000
,BF.FUNCTION_ID
,BF.CONTRACT_ID
,'MASS'
,'CUST'
,CURRENT DATE

,'MS'
,'R'
,CURRENT TIMESTAMP
,'DD'
,0
,0
,''
,''
,''
,''
,''
,''
,1
,CURRENT DATE
,CURRENT TIMESTAMP
,CURRENT CLIENT_USERID
,'SUBBU'
,''
,''
,'A'
FROM E2WDB2D.BUSINESS_FUNCTION BF WHERE BF.CONTRACT_ID=6;

AS THING I AM DOING FOR ANOTHER TABLE JOB_ITEM FOR THAT MULTIPLE INSERT WORKING FINE ,I AM GETTING PROBLEM WITH THIS JOB TABLE.
PLEASE DO THE NEEDFUL.

THANKS&REGARDS,
sUBRAHMANYAM

dav1mo · #2 (**permalink**) 05-29-09, 10:12

SUBRAHMANYAM,
Apparently, you have a unique index defined on the table you are inserting to and the data you are selecting, at least in some cases, returns multiple records. You either need to change your unique index or alter your select to just get a single row.
Dave

dav1mo · #3 (**permalink**) 05-29-09, 13:14

keep in mind that if your select gets multiple rows back each of those rows will use the same number for the sequence,E2WDB2D.JOB_ID_SEQ.

Dave

SUBRAHMANYAM · #4 (**permalink**) 05-30-09, 01:17

Select Returns Different Values For Sequence ,its Not Returning Duplicate Values.i Did Not Understand What Is The Problem With Unique Index On Tables While Inserting.

rahul_s80 · #5 (**permalink**) 06-01-09, 02:02

Quote:

Originally Posted by dav1mo

keep in mind that if your select gets multiple rows back each of those rows will use the same number for the sequence,E2WDB2D.JOB_ID_SEQ.

Dave

I dont have the permission to create a seq. @ office , but i am sure your statement is wrong.

Peter.Vanroose · #6 (**permalink**) 06-01-09, 05:31

The problem is *not* the presence of a unique index:
An SQLCODE -811 always indicates that the query uses a construct which can only be used with a single-row "sub-select", e.g. an INSERT INTO, or a scalar fullselect (like "WHERE c = (SELECT ...)").
(Often, a -811 can be curcumvented by turning the query into a cursor-based one.)
At first sight, your query does not have such a construct, because an INSERT with a SELECT may insert multiple rows at a time.
Actually, there is a fundamentally other reason why you receive a -811 here:
an expression "NEXT VALUE FOR ..." has a chronological aspect in it, i.e. the order of execution is important, while a multi-row select has no guarantee of ordering. So there is a conceptual incompatibility between the two, or otherwise said: there is no guarantee that the same query, executed twice in identical circumstances, will produce the same results.

It seems that this behaviour of DB2 is platform- and/or version-specific: I tried a simplified version of it on DB2 v8 for z/OS and it works, thereby generating non-equal values from the sequence object.

Code:

CREATE SEQUENCE s;
CREATE TABLE t (s int not null, v smallint);
INSERT INTO t SELECT next value for s, 1 FROM my_table;
SELECT * FROM t;

So maybe your version of DB2 is more standard-compliant with respect to the "predictability".
If you really want to do this in a multi-row-select situation, you'll have to implement this with a cursor.

Internet Services

Web Development

Digital Marketing

Consumer Tech