Select from table and function

Question

I have a function that returns a table. The returned table contains (among other things) a store_id. I can get the store_id for a particular transaction_id and city_id as follows:

select store_id from table(user2.f_get_store(15, 12345)); 
--where 15 and 12345 are city_id and transation_id

I have another table that contains a list of transactions (which includes transaction_id and city_id). I want a query that returns

store_id, city_id, transaction_id

for every entry in the transaction table. My first guess was:

select user2.f_get_store(city_id, transaction_id), city_id, transaction_id
from table_name;

(simplified away the unimportant details)

However, this yields an "ORA-00932: inconsistent datatypes" error. How do I need to structure this query?

(I'm using Oracle)

~~~~~EDIT~~~~~

When I try

select user2.f_get_store(city_id, transactioN_id) from table_name;

I get insufficient privileges error. I presume this is because f_get_store is owned by a different user than the one I am using.

(I edited the example code to show the function being owned by a different user)

Answer 1

I think you want a scalar subquery:

select (select store_id from table(user2.f_get_store(city_id, transaction_id))) store_id, city_id, transaction_id
from table_name;