Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2010
    Posts
    6

    Red face Unanswered: SQL0811N on update

    I tried to update one column from the following statement, but I got the error:
    SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.
    I know it is happened on the parts after "||(", because e.mfname returns more than one rows. So I think I need to use some loop or cursor to fix it. I tried different ways, but it is still not working. Can anyone help me on the code here? Thanks a lot.


    Here is my detail information.

    Database server = DB2/LINUXPPC 8.2.3

    Update Statement:
    UPDATE CATENTDESC
    SET KEYWORD = KEYWORD || ',' || ( SELECT e.mfname
    FROM CATENTDESC a, CATENTRY e
    WHERE LOCATE(e.mfname, a.keyword) = 0 and
    e.CATENTRY_ID = a.CATENTRY_ID AND
    e.FIELD4 = 'PRODUCT' and keyword is not null)
    where CATENTRY_ID in ( SELECT e.CATENTRY_ID
    FROM CATENTDESC a, CATENTRY e
    WHERE LOCATE(e.mfname, a.keyword) = 0 and
    e.CATENTRY_ID = a.CATENTRY_ID AND
    e.FIELD4 = 'PRODUCT' and keyword is not null);

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to add e.mfname(s) which are not in a.keyword,
    please try this example (or equivalent MERGE statement).

    (Not tested.)
    Code:
    UPDATE CATENTDESC a
       SET KEYWORD
         = KEYWORD ||
           ( SELECT REPLACE( REPLACE(
                       XMLSERIALIZE(
                          CONTENT XMLAGG( XMLELEMENT(NAME x , e.mfname) )
                          AS VARCHAR(1000)
                       )
                     , '<X>' , ',') , '</X>' , '')
               FROM CATENTRY e
              WHERE LOCATE(e.mfname, a.keyword) = 0
                and e.CATENTRY_ID = a.CATENTRY_ID
                AND e.FIELD4      = 'PRODUCT'
           ) 
     WHERE EXISTS
           ( SELECT 0
               FROM CATENTRY e
              WHERE LOCATE(e.mfname, a.keyword) = 0
                and e.CATENTRY_ID = a.CATENTRY_ID
                AND e.FIELD4      = 'PRODUCT'
           )
    ;

  3. #3
    Join Date
    Apr 2010
    Posts
    6
    Thank you fro your reply.

    When I run this parts,
    SELECT REPLACE( REPLACE(
    XMLSERIALIZE(
    CONTENT XMLAGG( XMLELEMENT(NAME x , e.mfname) )
    AS VARCHAR(1000)
    )
    , '<X>' , ',') , '</X>' , '')
    FROM CATENTDESC a, CATENTRY e
    WHERE LOCATE(e.mfname, a.keyword) = 0
    and e.CATENTRY_ID = a.CATENTRY_ID
    AND e.FIELD4 = 'PRODUCT'

    it returns as like this:
    Canari,Canari,Canari,Canari,Canari,Canari,Canari,C anari,Canari,Canari,Canari,Canari,Canari,Canari,Ca nari,Canari,Canari,Canari,adidas,adidas,adidas,adi das,adidas,adidas,adidas,adidas,adidas,adidas,adid as,adidas,adidas,adidas,adidas,adidas,adidas,adida s,adidas,adidas,adidas,adidas,adidas,Canari,Canari

    That is not what I want to update the keyword field. I just want to append one unique mfname value to the keyword value instead of some many duplicated values.
    Any ideas?

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    seems that you have some type of cartesian product. Ensure you are joining tables on their entire key, should there be any further conditions to limit what is returned by the query? You know your data and structures, we do not.
    Dave

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by javafun View Post
    Code:
    UPDATE CATENTDESC
    SET KEYWORD = KEYWORD || ',' || ( SELECT e.mfname
    FROM CATENTDESC a, CATENTRY e
    WHERE LOCATE(e.mfname, a.keyword) = 0 and
    e.CATENTRY_ID = a.CATENTRY_ID AND
    e.FIELD4 = 'PRODUCT' and keyword is not null)
    where CATENTRY_ID in ( SELECT e.CATENTRY_ID
    FROM CATENTDESC a, CATENTRY e
    WHERE LOCATE(e.mfname, a.keyword) = 0 and
    e.CATENTRY_ID = a.CATENTRY_ID AND
    e.FIELD4 = 'PRODUCT' and keyword is not null);
    You need to correlate the highlighted subselect to the table you are updating. If I remember the WCS schema correctly, catentry ID and language code should uniquely identify a record in catentdesc.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... I just want to append one unique mfname value to the keyword value instead of some many duplicated values.
    Try...

    Code:
    SELECT CATENTRY_ID
         , REPLACE( REPLACE(
              XMLSERIALIZE(
                 CONTENT XMLAGG( XMLELEMENT(NAME x , mfname) )
                 AS VARCHAR(1000)
              )
            , '<X>' , ',' ) , '</X>' , '' ) AS mfnames
      FROM (SELECT DISTINCT
                   e.mfname
                 , a.CATENTRY_ID
              FROM CATENTDESC a
                 , CATENTRY   e
             WHERE LOCATE(e.mfname , a.keyword) = 0
               AND e.CATENTRY_ID = a.CATENTRY_ID
               AND e.FIELD4      = 'PRODUCT'
           ) AS q
     GROUP BY
           CATENTRY_ID
    ;
    For UPDATE statement, you may want to remove "CATENTRY_ID" from the select list and "GROUP BY CATENTRY_ID" clause.
    Last edited by tonkuma; 04-21-10 at 05:49.

  7. #7
    Join Date
    Apr 2010
    Posts
    6

    Same error again

    I ran it and got the same SQL0811N error. Is there any way to use loop or cursor to retrieve one row at a time? Thanks a lot.


    UPDATE CATENTDESC b
    SET b.KEYWORD
    = b.KEYWORD ||( select REPLACE( REPLACE(
    XMLSERIALIZE(
    CONTENT XMLAGG( XMLELEMENT(NAME x , mfname) )
    AS VARCHAR(1000)
    )
    , '<X>' , ',' ) , '</X>' , '' ) AS mfnames
    FROM (SELECT DISTINCT
    e.mfname
    , a.CATENTRY_ID
    FROM CATENTDESC a
    , CATENTRY e
    WHERE LOCATE(e.mfname , a.keyword) = 0
    AND e.CATENTRY_ID = a.CATENTRY_ID
    AND e.FIELD4 = 'PRODUCT'
    ) AS q
    group by CATENTRY_ID)
    WHERE b.CATENTRY_ID in
    ( SELECT d.CATENTRY_ID
    FROM CATENTRY c, CATENTDESC d
    WHERE LOCATE(c.mfname, d.keyword) = 0
    and c.CATENTRY_ID = d.CATENTRY_ID
    AND c.FIELD4 = 'PRODUCT'
    )
    ;
    ------------------------------------------------------------------------------
    UPDATE CATENTDESC b SET b.KEYWORD = b.KEYWORD ||( select REPLACE( REPLACE( XMLSERIALIZE( CONTENT XMLAGG( XMLELEMENT(NAME x , mfname) ) AS VARCHAR(1000) ) , '<X>' , ',' ) , '</X>' , '' ) AS mfnames FROM (SELECT DISTINCT e.mfname , a.CATENTRY_ID FROM CATENTDESC a , CATENTRY e WHERE LOCATE(e.mfname , a.keyword) = 0 AND e.CATENTRY_ID = a.CATENTRY_ID AND e.FIELD4 = 'PRODUCT' ) AS q group by CATENTRY_ID) WHERE b.CATENTRY_ID in ( SELECT d.CATENTRY_ID FROM CATENTRY c, CATENTDESC d WHERE LOCATE(c.mfname, d.keyword) = 0 and c.CATENTRY_ID = d.CATENTRY_ID AND c.FIELD4 = 'PRODUCT' )

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES
    INTO statement is more than one row. SQLSTATE=21000

    SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.

    Explanation:

    One of the following caused the error:

    o Execution of an embedded SELECT INTO or VALUES INTO statement
    resulted in a result table of more than one row.

    o Execution of a scalar fullselect resulted in a result table
    of more than one row.

    Federated system users: this situation can be detected by
    federated server or by the data source.

    The statement cannot be processed.

    User Response:

    Ensure that the statement contains the proper condition
    specifications. If it does, there may be a data problem that is
    causing more than one row to be returned when only one is
    expected.

    Federated system users: isolate the problem to the data source
    failing the request (refer to the problem determination guide to
    determine which data source is failing to process the SQL
    statement) and examine the selection criteria and data for that
    object.
    sqlcode : -811
    sqlstate : 21000

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The problem in your update statement would be that you joined "CATENTDESC a" in the subquery in SET clause.
    And there was no relationship with "CATENTDESC a" and "UPDATE CATENTDESC b" in the subquery.

    Another issue might be that you used "group by CATENTRY_ID" in the subquery.
    I suggested .....
    For UPDATE statement, you may want to remove "CATENTRY_ID" from the select list and "GROUP BY CATENTRY_ID" clause.
    I want to recommend you referring to "UPDATE CATENTDESC b" directly.
    And if you couldn't refer "UPDATE CATENTDESC b" in your subquery, you can use TABLE keyword.

    In conclusion, try like this...
    Code:
    UPDATE CATENTDESC a
       SET KEYWORD
         = KEYWORD ||
           (SELECT REPLACE( REPLACE(
                      XMLSERIALIZE(
                         CONTENT XMLAGG( XMLELEMENT(NAME x , e.mfname) )
                         AS VARCHAR(1000)
                      )
                    , '<X>' , ',' ) , '</X>' , '' )
              FROM TABLE
                   (SELECT DISTINCT
                           e.mfname
                      FROM CATENTRY e
                     WHERE LOCATE(e.mfname , a.keyword) = 0
                       AND e.CATENTRY_ID = a.CATENTRY_ID
                       AND e.FIELD4      = 'PRODUCT'
                   ) AS e
           )
     WHERE EXISTS
           (SELECT 0
              FROM CATENTRY e
             WHERE LOCATE(e.mfname, a.keyword) = 0
               and e.CATENTRY_ID = a.CATENTRY_ID
               AND e.FIELD4      = 'PRODUCT'
           )
    ;

  9. #9
    Join Date
    Apr 2010
    Posts
    6
    I tried the new code one time, it looked like it worked. I will try more next week.

    Thanks everyone for the suggestions, especially thanks to Tonkuma. I really appreciate your time and effort.

  10. #10
    Join Date
    Apr 2010
    Posts
    2
    Hi
    Can You do a EXEC ROLLBACK in a CICS DB2 program ??

    Thank You

    Jorge

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by jdelmar View Post
    Hi
    Can You do a EXEC ROLLBACK in a CICS DB2 program ??

    Thank You

    Jorge
    Normally, when using CICS and DB2, there is a two-phase commit and CICS is the coordinator. Therefor SQL Commits and Rollbacks are not allowed.

    Instead, execute a CICS SYNCPOINT ROLLBACK command and it will rollback any uncommitted SQL.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by tonkuma View Post
    The problem in your update statement would be that you joined "CATENTDESC a" in the subquery in SET clause.
    And there was no relationship with "CATENTDESC a" and "UPDATE CATENTDESC b" in the subquery.

    Another issue might be that you used "group by CATENTRY_ID" in the subquery.
    I suggested .....


    I want to recommend you referring to "UPDATE CATENTDESC b" directly.
    And if you couldn't refer "UPDATE CATENTDESC b" in your subquery, you can use TABLE keyword.

    In conclusion, try like this...
    Code:
    UPDATE CATENTDESC a
       SET KEYWORD
         = KEYWORD ||
           (SELECT REPLACE( REPLACE(
                      XMLSERIALIZE(
                         CONTENT XMLAGG( XMLELEMENT(NAME x , e.mfname) )
                         AS VARCHAR(1000)
                      )
                    , '<X>' , ',' ) , '</X>' , '' )
              FROM TABLE
                   (SELECT DISTINCT
                           e.mfname
                      FROM CATENTRY e
                     WHERE LOCATE(e.mfname , a.keyword) = 0
                       AND e.CATENTRY_ID = a.CATENTRY_ID
                       AND e.FIELD4      = 'PRODUCT'
                   ) AS e
           )
     WHERE EXISTS
           (SELECT 0
              FROM CATENTRY e
             WHERE LOCATE(e.mfname, a.keyword) = 0
               and e.CATENTRY_ID = a.CATENTRY_ID
               AND e.FIELD4      = 'PRODUCT'
           )
    ;
    tonkuma, what for are you using ?

    Code:
    WHERE EXISTS
           (SELECT 0
              FROM CATENTRY e
             WHERE LOCATE(e.mfname, a.keyword) = 0
               and e.CATENTRY_ID = a.CATENTRY_ID
               AND e.FIELD4      = 'PRODUCT'
           )
    Lenny

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To prevent
    UPDATE CATENTDESC a
    SET KEYWORD
    = KEYWORD || NULL
    ...

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma View Post
    To prevent
    UPDATE CATENTDESC a
    SET KEYWORD
    = KEYWORD || NULL
    ...
    I knew your answer, but why you did not use IFNULL, or COALESCE functions ?
    Lenny

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Because, I couldn't convince that optimizer could eliminate all unnecessary operations for the rows which don't need to update, without the where clause.

    If I had enough time and patience, I might want to compare access paths of both update statements with/without the where clause.
    .

Tags for this Thread

Posting Permissions

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