I have a function that returns set of itemcodes. The result is correct but when I call it in stored procedure it doesn't show anything:
declare @itemgroupCode int
set @itemgroupCode=118
declare @todate datetime
set @todate='15.april.15'
declare @Group nvarchar(Max)
set @Group=(SELECT [dbo].[CurrProduction] (118,'15.APRIL.2015'))
select @Group
SELECT *
FROM (
SELECT
o.ItemCode as [Item],tm.ItemName as Dscription,o.Warehouse as Wr,obtn.LotNumber,
itl1.quantity as qty ,tm.U_Reusb,@Group as Grp
from OINM o inner join OITL on OITL.DocType = o .TransType and OITL.DocNum = o.BASE_REF and OITL.DocLine= o .DocLineNum and OITL .LocCode = o.Warehouse and o .ItemCode = OITL.ItemCode
inner join ITL1 on ITL1.LogEntry = OITL.LogEntry and ITL1.ItemCode=OITL.ItemCode
inner join OITM tm on tm.ItemCode = ITL1.ItemCode
inner join OITB on tm .ItmsGrpCod = OITB .ItmsGrpCod
INNER JOIN OWHS AS OH ON o.Warehouse=OH.WhsCode
inner join OBTN on OBTN.SysNumber = ITL1.SysNumber and OBTN.ItemCode = ITL1.ItemCode and tm.ManBtchNum = 'Y'
WHERE --tm.ItmsGrpCod=108 and
tm.ItemCode in (@Group)
and o.DocDate<=@todate ) as s
PIVOT
(
SUM(qty)
FOR Wr IN (
[DIS],[G&I],[GD-001],[GD-002],[GD-003],[GD-004],
[GD-005],[GD-006],[GD-007],[GD-008],[GD-009],[GD-010],
[GD-011],[GD-012],[GD-013],[GD-014],[GD-015],[GD-016],[GD-017],[GD-018],
[GD-019],[GD-020],[GD-021],[GD-022],[GD-023],[GD-024],[GD-025],[GD-026],
[GD-027],[GD-028],[GD-029],[GD-030],[GD-031],[GD-032],[GD-033],[GD-034],[GD-035],
[KHI AB],[KHI MM],[Loan],[LUB],[Main],[PCG - M1],[PCG - M2],
[PHS],[PMG],[PRO],[REJ],[REP],[REP-V],[Selling],[SKP]
)
)AS PVT
The function returns following when I paste in IN CLAUSE it gives me the correct result, but when I pass in the result of the function call, it doesn't show anything
Function returns:
'FYCT-00063','FYCM-00016','FYCM-00064','FYCF-00018','FYCM-00021','FYOG-00016','FYCM-00004','FYCM-00031','FYCM-00042' function code is following.
**
ALTER FUNCTION [dbo].[CurrProduction]
(
@unit varchar(10), @date datetime
)
RETURNS Varchar(Max)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar varchar(Max)
SELECT DISTINCT @ResultVar='''' + REPLACE(STUFF((
SELECT ',',+CAST(OWOR.ItemCode AS VARCHAR(10)) [text()]
FROM OWOR
where OWOR.PostDate=@date AND OWOR.U_Unit=CASE
when @unit=108 then 'Unit No1'
when @unit=118 then 'Unit No 2'
when @unit=119 then 'Unit No 3' END
AND OWOR.Status!='C'
FOR XML PATH('') , TYPE)
.value('.','NVARCHAR(MAX)'),1,1,' '),',',''',''')+''''
-- Return the result of the function
RETURN @RESULTVAR
END
**
In order to do what you want the function should be an Inline Table Valued function. Then you can simply call it in IN. For example:
CREATE TABLE ttinline(id int)
GO
INSERT INTO dbo.ttinline
VALUES (1),(2),(3)
CREATE FUNCTION fnInline()
RETURNS TABLE
AS
return SELECT * FROM dbo.ttinline
GO
SELECT * FROM dbo.ttinline WHERE id IN(SELECT * FROM dbo.fnInline())
Output:
id
1
2
3
EDIT:
ALTER FUNCTION [dbo].[CurrProduction]
(
@unit varchar(10), @date datetime
)
RETURNS TABLE
AS
RETURN
SELECT DISTINCT CAST(OWOR.ItemCode AS VARCHAR(10)) AS CODE
FROM OWOR
where OWOR.PostDate=@date AND OWOR.U_Unit=CASE
when @unit=108 then 'Unit No1'
when @unit=118 then 'Unit No 2'
when @unit=119 then 'Unit No 3' END
AND OWOR.Status!='C'
GO
DECLARE @itemgroupCode INT
SET @itemgroupCode = 118
DECLARE @todate DATETIME
SET @todate = '15.april.15'
SELECT *
FROM ( SELECT o.ItemCode AS [Item] ,
tm.ItemName AS Dscription ,
o.Warehouse AS Wr ,
obtn.LotNumber ,
itl1.quantity AS qty ,
tm.U_Reusb
FROM OINM o
INNER JOIN OITL ON OITL.DocType = o.TransType
AND OITL.DocNum = o.BASE_REF
AND OITL.DocLine = o.DocLineNum
AND OITL.LocCode = o.Warehouse
AND o.ItemCode = OITL.ItemCode
INNER JOIN ITL1 ON ITL1.LogEntry = OITL.LogEntry
AND ITL1.ItemCode = OITL.ItemCode
INNER JOIN OITM tm ON tm.ItemCode = ITL1.ItemCode
INNER JOIN OITB ON tm.ItmsGrpCod = OITB.ItmsGrpCod
INNER JOIN OWHS AS OH ON o.Warehouse = OH.WhsCode
INNER JOIN OBTN ON OBTN.SysNumber = ITL1.SysNumber
AND OBTN.ItemCode = ITL1.ItemCode
AND tm.ManBtchNum = 'Y'
WHERE --tm.ItmsGrpCod=108 and
tm.ItemCode IN ( SELECT CODE FROM [dbo].[CurrProduction](118, '15.APRIL.2015') )
AND o.DocDate <= @todate
) AS s PIVOT
( SUM(qty) FOR Wr IN ( [DIS], [G&I], [GD-001], [GD-002], [GD-003], [GD-004],
[GD-005], [GD-006], [GD-007], [GD-008], [GD-009],
[GD-010], [GD-011], [GD-012], [GD-013], [GD-014],
[GD-015], [GD-016], [GD-017], [GD-018], [GD-019],
[GD-020], [GD-021], [GD-022], [GD-023], [GD-024],
[GD-025], [GD-026], [GD-027], [GD-028], [GD-029],
[GD-030], [GD-031], [GD-032], [GD-033], [GD-034],
[GD-035], [KHI AB], [KHI MM], [Loan], [LUB], [Main],
[PCG - M1], [PCG - M2], [PHS], [PMG], [PRO], [REJ],
[REP], [REP-V], [Selling], [SKP] ) )AS PVT