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.
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.