How to check only first value of row returned from stored procedure in SQL query
If I have a stored procedure like this:
get_my_dep(empNum)
and returns one string ex:
call get_my_dep(567);
dep_code dep_year dep_name
66 2017 HR
How can I check only the first string value (dep_code) in my query like this:
SELECT *
FROM rmcandidate a INNER JOIN task b
ON a.task_code = b.task_code
WHERE get_my_dep(emp_num) != 0 -- here I want to check only the dep_code
AND b.active_flag = 1
+3
source to share
2 answers
Presumably a stored procedure is defined to return multiple values, as in:
create procedure get_my_dep(emp_num int)
returning int as dep_code, int as dep_year, char(8) as dep_name;
In this case, you can create a wrapper procedure that only returns one of the values, and then use that in the WHERE clause. For example:
create procedure get_my_dep_code(emp_num int)
returning int as dep_code;
define dc, dy int;
define dn char(8);
execute procedure get_my_dep(emp_num) into dc, dy, dn;
return dc;
end procedure;
An alternative would be to define a procedure to return a string type. For example:
create row type dep_code_t(dep_code int, dep_year int, dep_name char(8));
create procedure get_my_dep(emp_num int)
returning dep_code_t;
define dc, dy int;
define dn char(8);
...
return row(dc, dy, dn)::dep_code_t;
end procedure;
You can then directly reference the return type element of the row in the WHERE clause, as in:
WHERE get_my_dep(emp_num).dep_code != 0
+5
source to share
You can try using a virtual table:
SELECT
*
FROM
rmcandidate AS a
INNER JOIN task AS b
ON
a.task_code = b.task_code
WHERE
b.active_flag = 1
AND 0 !=
(
SELECT
vt1.dep_code
FROM
TABLE (get_my_dep(emp_num)) AS vt1 (dep_code, dep_year, dep_name)
)
;
This has been tested on Informix 12.10.
+3
source to share