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)

Agree with AJ. I don't know why your id fields would be chars, but that seems to be what it implies. Do you get same error when you do "select f_get_store(city_id, transaction_id) from table" ? — MJB
– MJB, Commented Apr 19, 2010 at 19:40
Probably either city_id or transaction_id is being treated like a string and implicitly being converted to a number - then bombing... To be more useful you should post the complete error message (hint on which type of conversion) and perhaps the code of the function. — AJ.
– AJ., Commented Apr 19, 2010 at 19:45
Complete error: `ORA-00932: inconsistent dataypes: expected - got USER2.F_GET_STORE_TYPE_TABLE 00932. 00000 - "inconsistent datatypes: expected %s got %s" *cause: *action: Error at Line: 13 Column 4" — David Oneill
– David Oneill, Commented Apr 19, 2010 at 19:49
@AJ's first comment. Can you be more specific? Casting the return of the function, or what is being passed in? — David Oneill
– David Oneill, Commented Apr 19, 2010 at 19:50

Dave Costa · Accepted Answer · 2010-04-20 12:08:47Z

2

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;

edited Apr 20, 2010 at 12:08

answered Apr 19, 2010 at 20:06

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Oneill Over a year ago

Will that work using user2.f_get_store(city_id, transation_id) instead of hardcoding the 15, 12345?

Dave Costa Over a year ago

Oops -- that's what I meant, just forgot to change the values when I copied your original query. Edited to show the final version.

Collectives™ on Stack Overflow

Select from table and function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related