Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: strange result while selecting from a table

    Hi,
    I executed the below select stmt alone and gives the corect result
    select pq5.incdnt_all.usrid from pq5.incdnt_all where pq5.incdnt_all.incdnt_id = 'Incdnt_ID'

    But when I execute it through store procedure like below , it returns multiple row.
    DECLARE USERID INTEGER DEFAULT 0;
    SET USERID=(select pq5.incdnt_all.usrid from pq5.incdnt_all where pq5.incdnt_all.incdnt_id = Incdnt_ID);

    How is it possible? Please provide some solution to resolve this issue.

    Thanks
    Surjya

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by surjyakp View Post
    Hi,


    But when I execute it through store procedure like below , it returns multiple row.
    DECLARE USERID INTEGER DEFAULT 0;
    SET USERID=(select pq5.incdnt_all.usrid from pq5.incdnt_all where pq5.incdnt_all.incdnt_id = Incdnt_ID);
    Your subselect is equivalent to
    Code:
    select pq5.incdnt_all.usrid from pq5.incdnt_all where 1 = 1
    which obviously returns all rows in the table. You should use some kind of naming convention that distinguishes table column names from variable and parameter names.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you sure it returns multiple rows, or are you just getting an error message that it might return multiple rows and therefore cannot be used in the context of statement you are attempting to use it in?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2010
    Posts
    26
    Yes instead of retriving one column I sued
    SET USERID=(select count(*) from pq5.incdnt_all where pq5.incdnt_all.incdnt_id = IncdntID);
    It gives total number of records in that table instead of for the particular incdnt_id. incdnt_id is primary key in that table.

    Thanks
    Surjya

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think your question is same as this thread for which I already answered same as Nick Ivanov's answer.

    http://www.dbforums.com/db2/1653194-...selecting.html
    Last edited by tonkuma; 02-01-10 at 20:46. Reason: Refer to Nick Ivanov.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you look at the 2 queries closely, you'll see that both are NOT identical. In one case, you compare with a string with the content "'Incdnt_ID'" and in the other case you use the content of the variable named Incdnt_ID because you don't have any quotes around the "Incdnt_ID".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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