Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: What is stored if the record does not exist

    Hello All

    My Query is the procedure is as follows

    select prs.id into xxemp
    from cm_pers AS prs, fg_dom AS dom
    where prs.split = dom.id and prs.username = UPPER('user1')
    and dom.ci_name = LOWER('DOMAIN')
    so my Question is what gets stored in xxemp if the select statement does not find any record with the conditions given above, i need to know this because after this i need to check a condition if xxemp contains a value then do this or else if it does not contain a value then do this. so what gets stored in xxemp if the record is not found in the table.

    Thanks

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    what gets stored in xxemp if the select statement does not find any record with the conditions given
    Nothing!
    Otherwise said, the host variable xxemp is not overwritten when the result table is empty.
    The *correct* way to test for an empty result table is by checking that SQLCODE equals +100. But in this case you *may* also first set xxemp to (say) 0, then run the query, then verify whether xxemp is still 0. But you'll never know whether this is because xxemp was not changed, or whether you actually found a row with prs.id = 0.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Are there any guarantees that xxemp won't be changed by the DBMS? At least I wouldn't rely on that behavior - it could just result in the strangest problems.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    Are there any guarantees that xxemp won't be changed by the DBMS? At least I wouldn't rely on that behavior - it could just result in the strangest problems.
    Indeed -- a well-known situation is an SQLCODE -811 which you receive when more than one row satisfies your request, but you used a "SELECT .. INTO .."
    In this case, typically, the *second* row's data will be returned.
    But as Knut Stolze correctly mentioned, no guarantees here!
    So, in any case, one *must* first check the SQLCODE and only use the returned data when SQLCODE=0.
    --_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
  •