Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered: Need no data found exception in db2

    Hi,
    Here is my procedure:
    Code:
    create or replace procedure  test
    (
       IN  javaLocale varchar(1020)
    )
    LANGUAGE SQL
    BEGIN
       DECLARE  xxlocaleid            char (20);
        
        select id into xxlocaleid 
    	from tp2.tpv_pub_locale locale 
    	where locale.java_locale = substr(javalocale,1,5); 
     	
       begin
         declare test cursor with return for
         select xxlocaleid from SYSIBM.SYSDUMMY1;
         open test;
       end;
    
    end
    What I want to do is when there is no data found, I want this procedure to return:
    Code:
     select 'Null',  'Null',   'inactive_rvw_cycle' rvw_cycle_name from SYSIBM.SYSDUMMY1
    How to do this in DB2.
    Last edited by Raghvendra; 07-13-13 at 08:18.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2012
    Posts
    6
    I tried condition handlers but no luck so far.
    Here's my code:

    create or replace procedure test
    (
    IN javaLocale varchar(1020)
    )
    LANGUAGE SQL
    BEGIN
    DECLARE xxlocaleid char (20);
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE p_sql_state CHAR(5) DEFAULT '00000';
    DECLARE p_sql_code INT DEFAULT 0;
    set p_sql_state = '';
    begin
    DECLARE CONTINUE HANDLER FOR SQLWARNING, NOT FOUND
    SELECT SQLSTATE, SQLCODE INTO p_sql_state , p_sql_code FROM SYSIBM.SYSDUMMY1;
    set p_sql_state = '';
    select id into xxlocaleid
    from tp2.tpv_pub_locale locale
    where locale.java_locale = substr(javalocale,1,5);
    begin
    declare test cursor with return for
    select xxlocaleid from SYSIBM.SYSDUMMY1;
    if (p_sql_state = '02000') then
    begin declare test1 cursor with return for
    select '000000' from SYSIBM.SYSDUMMY1;
    open test1;
    end;
    end if;
    open test;
    end;
    end;
    end

    Could you tell me what is wrong in this code?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Raghvendra View Post
    Could you tell me what is wrong in this code?
    You have no luck with it -- I guess that's as much as I can tell from your explanation. Some people believe a rabbit foot helps in such situation.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if you are not familiar with sp coding: have a look at samples directory below sqllib.
    it keeps many samples to be used..
    and when returning to this forum : always publish the message/code you get when creating this procedure
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You must be not so familiar with sp coding and SQL.

    Because, when I saw your code and explanations, I had some questions/issues, like...

    (1) You returned one column(xxlocaleid) when a row found in tp2.tpv_pub_locale.
    But, you want to return three columns('Null', 'Null', 'inactive_rvw_cycle' rvw_cycle_name) when not found.

    Why 'Null', 'Null' were neccesary when not found?


    (2) Your code might be able to be simplified, without handling the not found condition after execution of the query.

    For example, the body of your sp might be written like ...
    Code:
    /* Not tested.                 */
    /* Just an outline of my idea. */
    BEGIN
       DECLARE test cursor with return for
       SELECT COALESCE(locale.id , not_found_message)
        FROM  (VALUES 'inactive_rvw_cycle' ) AS f(not_found_message)
        LEFT  OUTER JOIN
              tp2.tpv_pub_locale locale
         ON   locale.java_locale = substr(javalocale,1,5);
       OPEN test;
    END

    (3) Why do you want to implement the routine as a stored procedure?
    If you built it as an UDF(user-defined-function), it could be more flexible to use the routine.
    For example, easier to include it into another query without declareing a(receiving) variable.

    An example of such UDF might be like this...
    Code:
    /* Not tested.                 */
    /* Just an outline of my idea. */
    CREATE OR REPLACE FUNCTION test
    ( IN  javaLocale varchar(1020)
    )
    RETURNS CHAR(20)
    LANGUAGE SQL
    READS SQL DATA
    NO EXTERNAL ACTION
    RETURN
       SELECT COALESCE(locale.id , not_found_message)
        FROM  (VALUES 'inactive_rvw_cycle' ) AS f(not_found_message)
        LEFT  OUTER JOIN
              tp2.tpv_pub_locale locale
         ON   locale.java_locale = substr(javalocale , 1 , 5)
    ;

Posting Permissions

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