March 26, 2016 at 1:06 am
Hi
I need some help with this function below.
I've not been able to get hold of my developer.
In the db he has a scalar function called get grnqty.
Now what get grnqty does is it gets the qty of the goods received for an item.The only problem is it gets the last qty received.
So if i received the same item twice in the same month it will only show the last qty received which is not what i want.
I need to be able to get more than the last qty.
Below is a sample of the function.
I've already tried in the select statement select top 2 but it doesn't work.
Any help will be appreciated
ALTER FUNCTION [dbo].[GetGRNQty_test2] (@ItemCode varchar(10))
RETURNS int AS
BEGIN
declare @pResult int
set @pResult = 0
declare @ta table (Divisionid int, Purchorderid int , grnid int, grndate datetime, qty int)
insert into @ta (Divisionid, Purchorderid, grnid, GRNDate, qty)
select top 1 S1.Divisionid, S1.Purchorderid, S1.grnid, G1.GRNDate, sum(Quantity)
from Grnstock S1
join GRN G1 on
S1.DivisionId = G1.DivisionId and
S1.Purchorderid = G1.Purchorderid and
S1.GRNId = G1.GRNId
join Item I on
I.Divisionid = S1.DivisionId and
I.DeptGroupid = S1.DeptGroupId and
I.DeptSubGroupid = S1.DeptSubGroupId and
I.Departmentid = S1.DepartmentId and
I.Itemid = S1.ItemId
where I.AlternateCode = @ItemCode
group by S1.Divisionid, S1.Purchorderid, S1.grnid, G1.GRNDate
order by GRNDate desc
select @pResult = qty from @ta
RETURN @pResult
end
GO
March 26, 2016 at 2:47 am
Your function is scalar valued so it will return only a single value.What you need to do is convert the scalar valued function to a inline table valued function so that it returns all the possible records matching the criteria.
Also you will have to make the application code changes where the function is consumed.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 26, 2016 at 2:57 am
Sachin Nandanwar (3/26/2016)
Your function is scalar valued so it will return only a single value.What you need to do is convert the scalar valued function to a inline table valued function so that it returns all the possible records matching the criteria.Also you will have to make the application code changes where the function is consumed.
Could you perhaps guide me on how to create a inline table valued function?
I'm not quite familiar on doing this
March 26, 2016 at 3:57 am
ALTER FUNCTION [dbo].[Getgrnqty_test2] (@ItemCode VARCHAR(10))
RETURNS TABLE
AS
BEGIN
RETURN
(SELECT S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate,
SUM(quantity)
FROM grnstock S1
JOIN grn G1
ON S1.divisionid = G1.divisionid
AND S1.purchorderid = G1.purchorderid
AND S1.grnid = G1.grnid
JOIN item I
ON I.divisionid = S1.divisionid
AND I.deptgroupid = S1.deptgroupid
AND I.deptsubgroupid = S1.deptsubgroupid
AND I.departmentid = S1.departmentid
AND I.itemid = S1.itemid
WHERE I.alternatecode = @ItemCode
GROUP BY S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate
);
END
GO
SELECT *
FROM dbo.Getgrnqty_test2(N'YourItemCode') --place the appropriate ItemCode
ORDER BY grndate DESC
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 26, 2016 at 4:45 am
Forgot the column alias for SUM in the earlier code.
ALTER FUNCTION [dbo].[Getgrnqty_test2] (@ItemCode VARCHAR(10))
RETURNS TABLE
AS
BEGIN
RETURN
(SELECT S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate,
SUM(quantity)Qty
FROM grnstock S1
JOIN grn G1
ON S1.divisionid = G1.divisionid
AND S1.purchorderid = G1.purchorderid
AND S1.grnid = G1.grnid
JOIN item I
ON I.divisionid = S1.divisionid
AND I.deptgroupid = S1.deptgroupid
AND I.deptsubgroupid = S1.deptsubgroupid
AND I.departmentid = S1.departmentid
AND I.itemid = S1.itemid
WHERE I.alternatecode = @ItemCode
GROUP BY S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate
);
END
GO
SELECT *
FROM dbo.Getgrnqty_test2(N'YourItemCode') --place the appropriate ItemCode
ORDER BY grndate DESC
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 26, 2016 at 5:18 am
Sachin Nandanwar (3/26/2016)
Forgot the column alias for SUM in the earlier code.
ALTER FUNCTION [dbo].[Getgrnqty_test2] (@ItemCode VARCHAR(10))RETURNS TABLE
AS
BEGIN
RETURN
(SELECT S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate,
SUM(quantity)Qty
FROM grnstock S1
JOIN grn G1
ON S1.divisionid = G1.divisionid
AND S1.purchorderid = G1.purchorderid
AND S1.grnid = G1.grnid
JOIN item I
ON I.divisionid = S1.divisionid
AND I.deptgroupid = S1.deptgroupid
AND I.deptsubgroupid = S1.deptsubgroupid
AND I.departmentid = S1.departmentid
AND I.itemid = S1.itemid
WHERE I.alternatecode = @ItemCode
GROUP BY S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate
);
END
GO
SELECT *
FROM dbo.Getgrnqty_test2(N'YourItemCode') --place the appropriate ItemCode
ORDER BY grndate DESC
Thanks sachin
March 26, 2016 at 10:09 am
Sachin Nandanwar (3/26/2016)
ALTER FUNCTION [dbo].[Getgrnqty_test2] (@ItemCode VARCHAR(10))
RETURNS TABLE
AS
BEGIN
RETURN
(SELECT S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate,
SUM(quantity)
FROM grnstock S1
JOIN grn G1
ON S1.divisionid = G1.divisionid
AND S1.purchorderid = G1.purchorderid
AND S1.grnid = G1.grnid
JOIN item I
ON I.divisionid = S1.divisionid
AND I.deptgroupid = S1.deptgroupid
AND I.deptsubgroupid = S1.deptsubgroupid
AND I.departmentid = S1.departmentid
AND I.itemid = S1.itemid
WHERE I.alternatecode = @ItemCode
GROUP BY S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate
);
END
GO
SELECT *
FROM dbo.Getgrnqty_test2(N'YourItemCode') --place the appropriate ItemCode
ORDER BY grndate DESC
I could be wrong and I know you've not been able to test it but I believe that iTVFs don't (as in NEVER) contain BEGIN/END and can't be made to.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2016 at 10:33 am
Yes Jeff you are right.It was a silly mistake.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 28, 2016 at 1:33 am
Jeff Moden (3/26/2016)
Sachin Nandanwar (3/26/2016)
ALTER FUNCTION [dbo].[Getgrnqty_test2] (@ItemCode VARCHAR(10))
RETURNS TABLE
AS
BEGIN
RETURN
(SELECT S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate,
SUM(quantity)
FROM grnstock S1
JOIN grn G1
ON S1.divisionid = G1.divisionid
AND S1.purchorderid = G1.purchorderid
AND S1.grnid = G1.grnid
JOIN item I
ON I.divisionid = S1.divisionid
AND I.deptgroupid = S1.deptgroupid
AND I.deptsubgroupid = S1.deptsubgroupid
AND I.departmentid = S1.departmentid
AND I.itemid = S1.itemid
WHERE I.alternatecode = @ItemCode
GROUP BY S1.divisionid,
S1.purchorderid,
S1.grnid,
G1.grndate
);
END
GO
SELECT *
FROM dbo.Getgrnqty_test2(N'YourItemCode') --place the appropriate ItemCode
ORDER BY grndate DESC
I could be wrong and I know you've not been able to test it but I believe that iTVFs don't (as in NEVER) contain BEGIN/END and can't be made to.
Yes,i had to remove the begin and end.But it worked.
I know this might sound stupid to you guys,but is it possible to run the iTVF in a statement where i'm running another query. I know for a iTVF you use have to select from.
March 28, 2016 at 1:54 am
You will have to use APPLY clause to send item code from the left input (outer table).
Something like this...
Select t1.*,t2.* from yourtable t1
Cross Apply dbo. Getgrnqty_test3 (t1.ItemCode) as t2
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 28, 2016 at 3:10 am
Thanks.Will try this out
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply