Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Unanswered: stored procedure

    I am running IDS 10 on AIX and the sample stored procedure below does not return the value (ie name) for the subquery in the select statement. When I execute the procedure, it returns the value NULL to emp_name(alias name).

    The whole SQL works fine outside the stored procedure on IDS 10 on AIX with/without alias name and it returns NULL when I execute the same SQL from the stored procedure.

    create procedure employee_pro()
    returning integer, integer, varchar(10) ;

    define e_id integer ;
    define e_num integer ;
    define e_name varchar(10) ;

    foreach cur_emp for
    select emp_id, dept_no, (select emp_name from employee where dept_no=9)as name
    into
    e_id, e_num, e_name
    from employee
    where
    dept_no = 9
    end foreach

    return e_id, e_num, e_name with resume ;
    end procedure ;


    Whereas the same code on IDS 1150 on Windows works without any issue. On windows it works with/without the alias name.

  2. #2
    Join Date
    Oct 2007
    Posts
    5

    why the subquery?

    I don't understand why you are doing a subquery for employee name? When you do the subquery and then assign the column name "name" to it, this could be causing your issue. the query is only looking at one table and the where cluase is the same in the query and the subquery.

    Why wouldn't you just do this:

    create procedure employee_pro()
    returning integer, integer, varchar(10) ;

    define e_id integer ;
    define e_num integer ;
    define e_name varchar(10) ;

    foreach cur_emp for
    select emp_id, dept_no, emp_name
    into
    e_id, e_num, e_name
    from employee
    where
    dept_no = 9
    end foreach

    return e_id, e_num, e_name with resume ;
    end procedure ;

Posting Permissions

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