Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37

    Unanswered: Multiple rows from a SELECT statement?

    I was under the impression a SELECT statement will fail if more than one row matches the selection criteria. Yet, I have someone who swears this works, although I have not seen any evidence to support this contention. It's a very simple SELECT, coming from a WebSphere SQL:

    SELECT ACCT_NBR
    FROM HOFF.T09_ACCOUNTS
    WHERE CUST_ID = W_CUST_ID

    I have no way of running this myself, except to code it in a COBOL program on the mainframe. I'm not sure that would prove anything.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    whenever there is more than 1 row - a cursor has to be declared and fetch has to be executed on cursor. otherwise a direct exec sql select into :hv ... can be used
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    Please explain "direct exec sql select into :hv ". My DB2 programming skills are fairly basic. Does a dynamic SQL sent through DB2 connect return all rows of a result set at once?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can run it via SPUFI, you can run it via DB2 Connect.

    A query that returns a single row is fetched directly (no client side cursor is instantiated). If more than one row is returned, then the client instantiates a cursor and each row is fetched and returned. It appears to everything higher in the client's protocol stack like the entire result set is returned at once.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    So, no matter what, I need a cursor somewhere. That's what I figured. Thanks.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have a look at application guide or cobol samples shipped how todo this
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

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

    Lightbulb Singleton Select VS Cursor

    Quote Originally Posted by Balr14 View Post
    I was under the impression a SELECT statement will fail if more than one row matches the selection criteria. Yet, I have someone who swears this works, although I have not seen any evidence to support this contention. It's a very simple SELECT, coming from a WebSphere SQL:

    SELECT ACCT_NBR
    FROM HOFF.T09_ACCOUNTS
    WHERE CUST_ID = W_CUST_ID

    I have no way of running this myself, except to code it in a COBOL program on the mainframe. I'm not sure that would prove anything.
    If you need only one row, you can get it using singleton select:

    Code:
    Exec SQL
     SELECT ACCT_NBR
     INTO :ws-acct-nbr:ws-acct-nbr-ind
     FROM HOFF.T09_ACCOUNTS
     WHERE CUST_ID = W_CUST_ID
     Fetch first row only 
    End-Exec
    
    if sqlcode < 0
      .........
    end-if
    Lenny

  8. #8
    Join Date
    Oct 2010
    Posts
    5

    It will give -811

    For a singleton select, if multiple rows are fetched for the condition in the where predicate, it will throw -811 and that's why we go for CURSOR in DB2 and process each fetched row.

    Hope this helps!!

  9. #9
    Join Date
    Jul 2009
    Posts
    150

    Exclamation Read before

    Quote Originally Posted by tapas16880 View Post
    For a singleton select, if multiple rows are fetched for the condition in the where predicate, it will throw -811 and that's why we go for CURSOR in DB2 and process each fetched row.

    Hope this helps!!
    Fetch first row only
    - prevents sqlcode = -811

    Kara

  10. #10
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    Quote Originally Posted by DB2Plus View Post
    - prevents sqlcode = -811

    Kara
    Now, that I recall!

  11. #11
    Join Date
    Oct 2010
    Posts
    5

    Other option

    From Performace point of view instead of "fetch first row only", people do not prefer writing a select query. Instead it is always recommended to do an "existence check".
    Select 1
    into :ws-xxxx
    from xxxxx
    where ??? = :?????

    This always works out faster!!!

  12. #12
    Join Date
    Jul 2009
    Posts
    150

    Thumbs down

    Quote Originally Posted by tapas16880 View Post
    From Performace point of view instead of "fetch first row only", people do not prefer writing a select query. Instead it is always recommended to do an "existence check".
    Select 1
    into :ws-xxxx
    from xxxxx
    where ??? = :?????

    This always works out faster!!!
    I'm sorry, but you don't understand how DB2 works.

    Even with yours "existence check" you can get -811.

    Kara

  13. #13
    Join Date
    Oct 2010
    Posts
    5

    Talking Thanks - U don't have to teach me DB2

    I have never said that "existence check" doesn't give -811. Did I? What solely I meant was instead of Select * or singleton select, it is always recommended to use "Existence check" when you just need to know whether the value is present in the table or not. Off course at the end of the SQL, you need to have ---

    EVALUATE SQLCODE
    WHEN +100
    ????????????????
    WHEN -811
    ??????????
    WHEN 0
    ?????????????
    WHEN OTHER
    ??????
    END-EVALUATE.

  14. #14
    Join Date
    Jul 2009
    Posts
    150
    Quote Originally Posted by tapas16880 View Post
    I have never said that "existence check" doesn't give -811. Did I? What solely I meant was instead of Select * or singleton select, it is always recommended to use "Existence check" when you just need to know whether the value is present in the table or not. Off course at the end of the SQL, you need to have ---

    EVALUATE SQLCODE
    WHEN +100
    ????????????????
    WHEN -811
    ??????????
    WHEN 0
    ?????????????
    WHEN OTHER
    ??????
    END-EVALUATE.
    Could be better:

    Code:
    EVALUATE TRUE
    SQLCODE
    WHEN SQLCODE = +100
    ????????????????
    WHEN 0
    WHEN -811
    ?????????????
    WHEN SQLCODE > 0
    ????????????????
    WHEN OTHER
    ??????
    END-EVALUATE.

Posting Permissions

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