Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: Getting Error While Inserting Multiple Rows

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  4. #4
    Join Date
    Feb 2009
    Posts
    4
    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.

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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.
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    Last edited by Peter.Vanroose; 06-01-09 at 06:51.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •