/*
@RuleName = IBM Sterling Commerce
@SoftwareName = IBM Sterling Commerce
@RuleStatus = Not Verified
@RuleCategory = IBM Sterling Commerce
@Vendor = IBM
*/

SELECT t.[DatasetID],   t.[DeviceInstanceID], t.[Version], t.[Edition]
FROM
(
		SELECT 1 AS [Source],
			ROW_NUMBER() OVER( PARTITION BY di.[DatasetID], di.[DeviceInstanceID] ORDER BY lk_proc.[Name] ) AS [Qualifier],
			di.[DatasetID],
			di.[DeviceInstanceID],
			NULL AS [Version],
			NULL AS [Edition]
		FROM [hub].[AttributeDeviceInstanceProcess] a_dip WITH( NOLOCK )
		JOIN [hub].[DeviceInstance] di WITH( NOLOCK ) ON di.[DeviceInstanceID] = a_dip.[DeviceInstanceID]
		JOIN [hub].[LookupProcess] lk_proc WITH( NOLOCK ) ON lk_proc.[LookupProcessID] = a_dip.[LookupProcessID]
		JOIN [hub].[LookupArgument] lk_arg WITH( NOLOCK ) on lk_arg.[LookupArgumentID] = a_dip.[LookupArgumentID]
		WHERE 
			 lk_arg.[Name] LIKE N'%sterling%'
) t
WHERE t.[Qualifier] = 1;
GO