tagtekin
asked on
combine the results from the function and the select statement
I have the function that returns 4 columns i am asked to return 5 vendor id from the select statement that tests the function. I have the following function and the select statement however i dont get 5 colmuns I only get one that comes from the vendors table.
/*
CREATE function fnDateRange
(@DateMin varchar(50), @datemax varchar(50))
returns table
return
(SELECT InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
*/
--drop function fnDateRange
--/*
select VENDORNAME, (SELECT * FROM dbo.fndaterange('april 10 2002', 'april 18 2002')AS Q
where o.VENDORID = Q.VENDORID)as maxe
FROM VENDORS AS o
--*/
/*
CREATE function fnDateRange
(@DateMin varchar(50), @datemax varchar(50))
returns table
return
(SELECT InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
*/
--drop function fnDateRange
--/*
select VENDORNAME, (SELECT * FROM dbo.fndaterange('april 10 2002', 'april 18 2002')AS Q
where o.VENDORID = Q.VENDORID)as maxe
FROM VENDORS AS o
--*/
Hi tagtekin,
I think You should use INNER JOIN between tour table and function.
But the problem here is i didn't dind any proper joining keye in your function. It's better to return one unique key, something like VendorId from your funcion and do a join
SELECT VendorName, t.*
FROM Vendors
INNER JOIN dbo.fnDateRange (...) t
on t.VendorID = Vendors.vendorID
Aneesh
I think You should use INNER JOIN between tour table and function.
But the problem here is i didn't dind any proper joining keye in your function. It's better to return one unique key, something like VendorId from your funcion and do a join
SELECT VendorName, t.*
FROM Vendors
INNER JOIN dbo.fnDateRange (...) t
on t.VendorID = Vendors.vendorID
Aneesh
good point:
CREATE function fnDateRange
(@DateMin varchar(50), @datemax varchar(50))
returns table ( VendorIDas int , InvoiceTotal as numeric(12,4), InvoiceNumber as int, Invoicedate as date, Balance as numeric(12,4))
return
(SELECT Vendorid, InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
CREATE function fnDateRange
(@DateMin varchar(50), @datemax varchar(50))
returns table ( VendorIDas int , InvoiceTotal as numeric(12,4), InvoiceNumber as int, Invoicedate as date, Balance as numeric(12,4))
return
(SELECT Vendorid, InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
CREATE function fnDateRange
(@DateMin varchar(50), @datemax varchar(50))
returns table ( VendorID as int , InvoiceTotal as numeric(12,4), InvoiceNumber as int, Invoicedate as date, Balance as numeric(12,4)) -- missed s space here
return
(SELECT Vendorid, InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
(@DateMin varchar(50), @datemax varchar(50))
returns table ( VendorID as int , InvoiceTotal as numeric(12,4), InvoiceNumber as int, Invoicedate as date, Balance as numeric(12,4)) -- missed s space here
return
(SELECT Vendorid, InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
ASKER
yes but this function created returns the vendor id directly from the function i needed to return that column from the select statement.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did this and add it made the change with your comment and it worked. THank you. I m posting another one with my other problem. Hope to see your comments.
/*
CREATE function fnDateRange
(@DateMin varchar(50), @datemax varchar(50))
returns table
return
(SELECT Vendorid, InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
*/
--*/
--drop function fnDateRange
--select * from dbo.fndaterange('april 10 2002', 'april 18 2002')
SELECT Vendors.VendorName, Vendors.VendorID, t.InvoiceTotal, t.InvoiceNumber, t.InvoiceDate,t.balance
FROM Vendors
INNER JOIN dbo.fnDateRange ('april 10 2002', 'april 18 2002')as t on t.VendorID = Vendors.vendorID
/*
CREATE function fnDateRange
(@DateMin varchar(50), @datemax varchar(50))
returns table
return
(SELECT Vendorid, InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
*/
--*/
--drop function fnDateRange
--select * from dbo.fndaterange('april 10 2002', 'april 18 2002')
SELECT Vendors.VendorName, Vendors.VendorID, t.InvoiceTotal, t.InvoiceNumber, t.InvoiceDate,t.balance
FROM Vendors
INNER JOIN dbo.fnDateRange ('april 10 2002', 'april 18 2002')as t on t.VendorID = Vendors.vendorID
are you getting any error ?
ASKER
no I was getting since there was no vendorid coming from the function but i added andsolved the problem.
Ok.. now it seems that you are solved the problem
ASKER
yes thank you very much
(@DateMin varchar(50), @datemax varchar(50))
returns table ( InvoiceTotal as numeric(12,4), InvoiceNumber as int, Invoicedate as date, Balance as numeric(12,4))
return
(SELECT InvoiceTotal, InvoiceNumber, InvoiceDate,InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Invoices
WHERE InvoiceDate BETWEEN @DateMin AND @DateMax)
select o.VENDORNAME, q.*
from vendors o
join dbo.fndaterange('april 10 2002', 'april 18 2002') AS Q
on o.VENDORID = Q.VENDORID