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.

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 ;