CODEWITHSUNDEEP
sqlstored-proceduresinformix
Anyname Donotcare
Anyname Donotcare

Reputation: 11423

How to check only the first value of the row returned from stored procedure in a SQL query

If I have a stored procedure like this:

get_my_dep(empNum)

and it returns one row ex:

call get_my_dep(567);


dep_code  dep_year  dep_name
66        2017       HR

How can I check only the first value of the row (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

Upvotes: 3

Views: 416

Answers (2)

Luís Marques
Luís Marques

Reputation: 1451

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 was tested on Informix 12.10 .

Upvotes: 3

Simon Riddle
Simon Riddle

Reputation: 1116

Presumably the stored procedure is defined as returning 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 could create a wrapper procedure that returns only 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 could be to define the procedure to return a row 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;

It is then possible to directly reference an element of the returned row type in a WHERE clause as in:

WHERE get_my_dep(emp_num).dep_code != 0

Upvotes: 5

Related Questions