Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32

    Unanswered: Problem using GET DIAGNOSTICS

    Hi,

    I'm trying to use GET DIAGNOSTICS to know if a select returns or not a row, in a function that contains a statement like this :

    SET ( vVariable ) = ( select colunm
    from table
    fetch first 1 rows only );

    GET DIAGNOSTICS vRows = ROW_COUNT;

    But in this case always GET DIAGNOSTICS returns "0", someone knows what's happening ?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The way I read the manual on GET DIAGNOSTICS is that it returns information about the previous SQL statement. It does not mention anything about subselects. If the statement is a INSERT, UPDATE, OR DELETE row_count returns the number of rows affected.

    I think in your example, you statement is a SET, and no rows are affected by that.

    HTH

    Andy

  3. #3
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    But you know another way to get the number of rows without using GET DIAGNOSTICS, because i need to know if my select returns any rows.

    I've tried to use SELECT INTO, and then use GET DIAGNOSTICS but i think the statement SELECT INTO doesn't work in a function.

    I'm waiting for reply, thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why not use:
    select count(*) from mytable where (same as other select stmt)

    Andy

Posting Permissions

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