January 23, 2009 at 2:02 am
Hi all,
Please, could you urgently help me out with the code below, I know it's not working because of the LIKE function am using on the sub query, the solution (as resulted) would have been that of making use of the IN function, but on doing that, I obviously don't get all the needed results. Thanks in advance.
-::::::::::::::::::::::::::::::::::::::::::::::::The code::::::::::::::::::::::::::::::::::::::::::
declare @collection varchar(30)
declare @Start int
declare @End int
set @Start = (Select MIN(id) from Sw)
set @End = (Select MAX(id) from Sw)
set @collection = 'MyColl'
while @Start <= @End
begin
Select DISTINCT sys.Netbios_Name0 AS [Netbios Name],
fcm.SiteCode AS [Site Code],
sys.User_Domain0 AS [User Domain],
sys.User_Name0 AS [User Name],
sys.Operating_System_Name_and0 AS [Operating System Name],
arp.DisplayName0 AS [Display Name],
SUBSTRING(suser_sname(), 7, 20) AS [Created By],
getdate()AS DataPulledDate
FROM SMS.dbo.v_R_System sys
JOIN SMS.dbo.v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN SMS.dbo.v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID=@collection and DisplayName0 LIKE (Select '%'+ProductName+'%' COLLATE Latin1_General_CI_AI AS ProductName from Sw)
set @Start = @Start + 1
set @End = @End - @End
end
--:::::::::::::::::::::::::::::::The error::::::::::::::::::::::::::::::::::::::::
Msg 512, Level 16, State 1, Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
January 23, 2009 at 2:21 am
greg eze (1/23/2009)
FROM SMS.dbo.v_R_System sys
JOIN SMS.dbo.v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN SMS.dbo.v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID=@collection and DisplayName0 LIKE (Select '%'+ProductName+'%' COLLATE Latin1_General_CI_AI AS ProductName from Sw)
Rewrite above FROM caluse as below......
-----------------------------------------------------
FROM SMS.dbo.v_R_System sys
JOIN SMS.dbo.v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN SMS.dbo.v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
JOIN Sw ON DisplayName0 like '%'+SW.ProductName+'%' COLLATE Latin1_General_CI_AI AS
WHERE fcm.CollectionID=@collection
Regards,
Nitin
January 23, 2009 at 2:37 am
Many thanks Nitin! It worked like a charm.
You turned my nightmare to happiness. Have a blessed day.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply