Andrew J Winkler Andrew J Winkler - 2 years ago 95
SQL Question

Ambiguous column when executing MS SQL Server function?

SQL Fiddle is currently down regarding MS SQL Server code, so here is a dropbox link to a .txt containing the DDL 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 ambiguous.

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