I have created a function in my database using this query:
我已经在我的数据库中使用这个查询创建了一个函数:
CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(MAX) -- List of delimited items
, @sDelimiter VARCHAR(MAX) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(MAX))
BEGIN
DECLARE @sItem VARCHAR(MAX)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
And SQL Server returned : Command(s) completed successfully.
SQL Server返回:命令成功完成。
Then I tried to run this query:
然后我尝试运行这个查询:
SELECT * FROM maj_Posts a
WHERE FeedID = (SELECT dbo.fnSplit(b.FeedIDs) FROM maj_Magazines b WHERE OwnerID = 1)
ORDER BY countOfComments DESC
It returned an error: Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplit", or the name is ambiguous.
它返回了一个错误:msg4121,级别16,状态1,第1行无法找到“dbo”列或用户定义函数或聚合“dbo”。fnSplit",或名称不明确。
Note that b.FeedIDs
is an string contains comma-separated numbers, like this : 1,2,4
请注意,b。FeedIDs是一个包含逗号分隔数的字符串,例如:1、2、4。
And I want to get rows from maj_Posts
which their a.FeedID
is one of the numbers in b.FeedIDs
... (For example, if b.FeedIDs
is 1,2,4
, I need rows from maj_Posts
that their FeedID
is 1 or 2 or 4.)
我想从maj_Posts中获取行。菲迪德是b.FeedIDs…(例如,如果b。FeedIDs是1、2、4,我需要来自maj_Posts的行,它们的衰弱是1、2或4)。
What is the problem?
这个问题是什么?
2 个解决方案
#1
3
Perhaps you meant this:
也许你是这样的:
SELECT * FROM maj_Posts a
LEFT OUTER JOIN dbo.maj_Magazines b
ON 1 = 1 AND b.OwnerID = 1
OUTER APPLY dbo.fnSplit(b.FeedIDs, ',') AS s
WHERE a.FeedID = s.item
ORDER BY countOfComments DESC;
Or
或
SELECT * FROM maj_Posts a
WHERE FeedID IN
(
SELECT item FROM maj_Magazines AS b
CROSS APPLY dbo.fnSplit(b.FeedIDs, ',')
WHERE b.OwnerID = 1
)
ORDER BY countOfComments DESC;
#2
1
USE [oberoi]
GO
/****** Object: StoredProcedure [dbo].[sp_AssetReport] Script Date: 03/23/2013 12:27:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_AssetReport]
AS
BEGIN
SELECT distinct am.assetid,am.computerName,
ao.os_name,ao.user_name,ao.reg_code, ao.reg_org, ao.reg_to,
ao.localization, ao.product_key,
-- an.ip_address,
amem.physical_mem,amem.virtual_mem,(Select top 1 ip_address from Asset_Network where assetId=am.assetid) as ip_address,ap.processor_name,ap.speed, ap.manufacturer,
api.serial_number,api.product_name, api.product_manufacturer,am.TagNo,am.PONo,dbo.capacity_checkn(apd.capacity) as capacity,am.Location,am.AssetOwner,am.CompanyCode,am.PurchaseDate,am.AssetCategory,am.Remarks
FROM Asset_mst As am INNER JOIN
-- Asset_Network AS an ON am.assetId = an.assetId INNER JOIN
--Asset_Inventory As AI on an.assetId=AI.assetId INNER JOIN
Asset_OperatingSystem AS ao on am.assetId=ao.assetId JOIN
Asset_Memory AS amem ON ao.assetId = amem.assetId INNER JOIN
Asset_Processor AS ap ON amem.assetId = ap.assetId INNER JOIN
Asset_ProductInfo AS api ON ap.assetId = api.assetid INNER JOIN
Asset_PhysicalDrive AS apd ON am.assetId = apd.assetid
where CONVERT(datetime,CONVERT(char(12),Am.createdatetime )) = CONVERT(datetime,CONVERT(char(12),ao.inventory_date ))
--And CONVERT(datetime,CONVERT(char(12),an.inventory_date )) = CONVERT(datetime,CONVERT(char(12),AI.inventoryDate ))
--And CONVERT(datetime,CONVERT(char(12),an.inventory_date )) = CONVERT(datetime,CONVERT(char(12),ao.inventory_date ))
And
CONVERT(datetime,CONVERT(char(12),Ao.inventory_date )) = CONVERT(datetime,CONVERT(char(12),amem.inventory_date ))
And CONVERT(datetime,CONVERT(char(12),amem.inventory_date )) = CONVERT(datetime,CONVERT(char(12),ap.inventory_date ))
And CONVERT(datetime,CONVERT(char(12),ap.inventory_date )) = CONVERT(datetime,CONVERT(char(12),api.inventory_date ))
And CONVERT(datetime,CONVERT(char(12),api.inventory_date )) = CONVERT(datetime,CONVERT(char(12),apd.inventory_date ))
and am.Isdelete=0
enter code here
End
#1
3
Perhaps you meant this:
也许你是这样的:
SELECT * FROM maj_Posts a
LEFT OUTER JOIN dbo.maj_Magazines b
ON 1 = 1 AND b.OwnerID = 1
OUTER APPLY dbo.fnSplit(b.FeedIDs, ',') AS s
WHERE a.FeedID = s.item
ORDER BY countOfComments DESC;
Or
或
SELECT * FROM maj_Posts a
WHERE FeedID IN
(
SELECT item FROM maj_Magazines AS b
CROSS APPLY dbo.fnSplit(b.FeedIDs, ',')
WHERE b.OwnerID = 1
)
ORDER BY countOfComments DESC;
#2
1
USE [oberoi]
GO
/****** Object: StoredProcedure [dbo].[sp_AssetReport] Script Date: 03/23/2013 12:27:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_AssetReport]
AS
BEGIN
SELECT distinct am.assetid,am.computerName,
ao.os_name,ao.user_name,ao.reg_code, ao.reg_org, ao.reg_to,
ao.localization, ao.product_key,
-- an.ip_address,
amem.physical_mem,amem.virtual_mem,(Select top 1 ip_address from Asset_Network where assetId=am.assetid) as ip_address,ap.processor_name,ap.speed, ap.manufacturer,
api.serial_number,api.product_name, api.product_manufacturer,am.TagNo,am.PONo,dbo.capacity_checkn(apd.capacity) as capacity,am.Location,am.AssetOwner,am.CompanyCode,am.PurchaseDate,am.AssetCategory,am.Remarks
FROM Asset_mst As am INNER JOIN
-- Asset_Network AS an ON am.assetId = an.assetId INNER JOIN
--Asset_Inventory As AI on an.assetId=AI.assetId INNER JOIN
Asset_OperatingSystem AS ao on am.assetId=ao.assetId JOIN
Asset_Memory AS amem ON ao.assetId = amem.assetId INNER JOIN
Asset_Processor AS ap ON amem.assetId = ap.assetId INNER JOIN
Asset_ProductInfo AS api ON ap.assetId = api.assetid INNER JOIN
Asset_PhysicalDrive AS apd ON am.assetId = apd.assetid
where CONVERT(datetime,CONVERT(char(12),Am.createdatetime )) = CONVERT(datetime,CONVERT(char(12),ao.inventory_date ))
--And CONVERT(datetime,CONVERT(char(12),an.inventory_date )) = CONVERT(datetime,CONVERT(char(12),AI.inventoryDate ))
--And CONVERT(datetime,CONVERT(char(12),an.inventory_date )) = CONVERT(datetime,CONVERT(char(12),ao.inventory_date ))
And
CONVERT(datetime,CONVERT(char(12),Ao.inventory_date )) = CONVERT(datetime,CONVERT(char(12),amem.inventory_date ))
And CONVERT(datetime,CONVERT(char(12),amem.inventory_date )) = CONVERT(datetime,CONVERT(char(12),ap.inventory_date ))
And CONVERT(datetime,CONVERT(char(12),ap.inventory_date )) = CONVERT(datetime,CONVERT(char(12),api.inventory_date ))
And CONVERT(datetime,CONVERT(char(12),api.inventory_date )) = CONVERT(datetime,CONVERT(char(12),apd.inventory_date ))
and am.Isdelete=0
enter code here
End