LIKE with IN/list of entries

[B@Th*nG]

Hi all,

I have a query whereby i need to return some entries from a table but the actual field is a string comprising other elements, such as a date. That means i need to do some sort of like, but also with an IN/list of entries. Having produced the script SQL Server Management Studio is telling me that the subquery is returning more than 1 record, which is true, but is there any way around this. See query below:

select ah.changedate, ah.ChangeDescription, ah.UserName, ad.Auditid, ad.sourcekey, ad.OldValue, ad.NewValue
from AuditDetail ad,
     auditheader ah
where ah.AuditId = ad.AuditId
AND ad.sourcekey like CONVERT(VARCHAR(20),(select distinct individualpolicyid from SplitBillingLink AS SB inner join Policy AS POL 
		on SB.EmployeeCategoryPolicyId = POL.PolicyId where PolicyStatus = 'L' and IndividualPolicyEffectiveDate = EffectiveDate)) + '~19%'
order by ad.Auditid

any help much appreciated.

Many thanks,

B@

 

[planty]

If you're in control of the data then you shouldn't really have a column which contains multiple pieces of information like that.

Assuming you're not in control of that data, could you JOIN on the subquery data and then use LIKE in the JOIN clause? e.g.

SELECT FROM xxxxxx INNER JOIN SubQuery ON SubQuery.individualpolicyid LIKE ad.sourcekey

 

[oneilldo]

Or you put the individual distinct policyid in a temp table the join to it rather than a sub query

 

[davetherave2]

WITH SourceKeyCTE AS 
(
	SELECT 
		DISTINCT CONVERT(NVARCHAR(20),individualpolicyid) + '~19%' AS [PolicyID]
	FROM 
		SplitBillingLink AS SB 
		INNER JOIN 
			Policy AS POL 
			ON 
				SB.EmployeeCategoryPolicyId = POL.PolicyId 
	WHERE 
		PolicyStatus = 'L' and IndividualPolicyEffectiveDate = EffectiveDate)
)




SELECT 
	  ah.changedate
	, ah.ChangeDescription
	, ah.UserName
	, ad.Auditid
	, ad.sourcekey
	, ad.OldValue
	, ad.NewValue
	, sk.PolicyID
FROM 
	auditheader ah
	LEFT JOIN 
		AuditDetail ad
		ON 
			ah.AuditId = ad.AuditId
	LEFT JOIN 
		SourceKeyCTE sk
		ON 
			ad.sourcekey LIKE sk.PolicyID
WHERE 
	sk.PolicyID IS NOT NULL 
ORDER BY 
	ad.Auditid;

I have made some assumptions but would this work for you?

If you then wanted to see one record for any potential matches then you could just simply group the results.

 

[B@Th*nG]

thanks guys, i appreciate all your help!!

B@