Andrew J Winkler Andrew J Winkler - 1 year ago 75
SQL Question

Ambiguous column when executing SQL Server function?

SQL Fiddle is currently down regarding SQL Server code, so here is a dropbox link to a

containing the
to create the schema I'm using:

I'm studying for an exam and struggling with an example problem.

Create a function to calculate the total cost of a software package (given the software package’s name) installed in each department in the last three years. Check whether the name is accurate.

Here's my solution:

create function total_cost_of_sofpack_in_lst3yrs
(@pack_name varchar(20))
returns table
select sum(packcost) as total_pk_cost, location
from package
inner join software on package.PACK = software.PACK
inner join pc on software.tagnum = pc.tagnum
where package.PACKNAME = @pack_name
and software.INSTDATE > dateadd(year, -3, getdate())
group by pc.location)

select dbo.total_cost_of_sofpack_in_lst3yrs('Manta') as R

The statement to create the function passes, but when I try to execute it via a test scenario, I get the following error:

Cannot find either column "dbo" or the user-defined function or
aggregate "dbo.total_cost_of_sofpack_in_lst3yrs", or the name is

Thanks for helping.

Answer Source

Since the return type is TABLE from the function, you need to call as

SELECT * FROM dbo.total_cost_of_sofpack_in_lst3yrs('Manta') 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download