Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2011
    Posts
    84

    Unanswered: stored procedure problem

    Hi can you help me please,

    I want to make a stored procedure in ibm data studio,to select in the employee table that there last name starts with vowel letter.
    how can i make an sql statement on this?I use this statement

    create procedure vowe()
    dynamic result sets 1
    language sql

    begin

    select lastname from employee
    where lastname like '[AEIOU]%'

    end
    but it's not working,can you please help me on this...please,
    by the way how can i see the records or the output?like we did in querring in the db2 command editor.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This should answer both you questions:

    Code:
    create procedure vowel()
    dynamic result sets 1
    language sql
    
    begin
    DECLARE CURSOR1 CURSOR WITH RETURN FOR
    select lastname from employee
    where ucase(substr(lastname,1,1)) in ('A','E','I','O','U');
    
    OPEN CURSOR1;
    end
    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have no experience of data studio.

    But, after seeing your requirements.
    ... to select in the employee table that there last name starts with vowel letter
    I thought that a table function might be better than a stored procedure.

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE
      FUNCTION vowel()
      RETURNS TABLE
    ( lastname VARCHAR(15) )
      LANGUAGE SQL
      DETERMINISTIC
      NO EXTERNAL ACTION
    RETURN
    SELECT lastname
     FROM  employee
     WHERE LOCATE( LEFT(lastname , 1) , 'AEIOU' ) > 0
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Sample usage:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( vowel() ) t;
    ------------------------------------------------------------------------------
    
    LASTNAME       
    ---------------
    O'CONNELL      
    ADAMSON        
    
      2 record(s) selected.

  4. #4
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by ARWinner View Post
    This should answer both you questions:

    Code:
    create procedure vowel()
    dynamic result sets 1
    language sql
    
    begin
    DECLARE CURSOR1 CURSOR WITH RETURN FOR
    select lastname from employee
    where ucase(substr(lastname,1,1)) in ('A','E','I','O','U');
    
    OPEN CURSOR1;
    end
    Andy
    Hello sir Andy,

    It's working i saw the output beside in the parameter tab in the lower level part..Thank you so much sir....sir can i ask something what is declare cursor1 cursor with return for?please help me to enligthen my mind.

    more power to you...


    Best Regards,

    Jemz

  5. #5
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    I have no experience of data studio.

    But, after seeing your requirements.


    I thought that a table function might be better than a stored procedure.

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE
      FUNCTION vowel()
      RETURNS TABLE
    ( lastname VARCHAR(15) )
      LANGUAGE SQL
      DETERMINISTIC
      NO EXTERNAL ACTION
    RETURN
    SELECT lastname
     FROM  employee
     WHERE LOCATE( LEFT(lastname , 1) , 'AEIOU' ) > 0
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Sample usage:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( vowel() ) t;
    ------------------------------------------------------------------------------
    
    LASTNAME       
    ---------------
    O'CONNELL      
    ADAMSON        
    
      2 record(s) selected.
    Hi sir Tonkuma,


    Sir it's working also, but can i ask something favor is it okay to put some comments
    on this line or please help me to undersand the Deterministic,No external Action.I have not yet seen this.and i am still beginner in making sp.please help me..Thank you so much for your help.

    CREATE OR REPLACE
    FUNCTION vowel()
    RETURNS TABLE
    ( lastname VARCHAR(15) )
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    RETURN
    SELECT lastname
    FROM employee
    WHERE LOCATE( LEFT(lastname , 1) , 'AEIOU' ) > 0
    ;

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by jemz View Post
    Hello sir Andy,

    It's working i saw the output beside in the parameter tab in the lower level part..Thank you so much sir....sir can i ask something what is declare cursor1 cursor with return for?please help me to enligthen my mind.

    more power to you...


    Best Regards,

    Jemz
    In order to get a result set from a stored procedure, you need a CURSOR. You declare the cursor, then leave it open for the result to be returned.

    Andy

  7. #7
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by ARWinner View Post
    In order to get a result set from a stored procedure, you need a CURSOR. You declare the cursor, then leave it open for the result to be returned.

    Andy
    Hello sir,

    Thank you so much for this...it really helps me...more power to you always.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... please help me to undersand the Deterministic,No external Action.
    Please read documents.

    For example,
    you can find the Descriptions of DETERMINISTIC and NO EXTERNAL ACTION in here....

    CREATE PROCEDURE (SQL) statement
    CREATE FUNCTION (SQL scalar, table, or row) statement

  9. #9
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    Please read documents.

    For example,
    you can find the Descriptions of DETERMINISTIC and NO EXTERNAL ACTION in here....

    CREATE PROCEDURE (SQL) statement
    CREATE FUNCTION (SQL scalar, table, or row) statement

    Sir thank you for the links...how to marked this trhead as solved?

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by jemz View Post
    Sir thank you for the links...how to marked this trhead as solved?
    You just did.

    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
  •