Andrew J Winkler Andrew J Winkler - 6 months ago 13
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

.txt
containing the
DDL
to create the schema I'm using:

https://www.dropbox.com/s/6si4r37449q3ajb/DDL.txt?dl=0

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
as
return(
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)
go

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
ambiguous.


Thanks for helping.

Answer

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

SELECT * FROM dbo.total_cost_of_sofpack_in_lst3yrs('Manta') 
Comments