Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Combining variables in a query

We have been using this query to search for contacts by one part of their address. I have found that it will miss some results. If I search on a city and leave zip and county blank I should get all the results for that city regardless of whether they have a zip or county. For some BPu2019s (but not all) it will not include them unless there is something in their county field.

Am I using the u2018ANDu2019 statement and variable correctly?

SELECT T0.CardCode as 'BP Code', T0.CardName as 'BP Name', T0.CardType as 'Type',

T1.AdresType as 'Type', T1.Street, T1.Block, T1.City, T1.County, T1.ZipCode as 'Postcode', T1.Country

FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode

WHERE T1.ZipCode like '%''[%0]''%'

AND T1.City like '%''[%1]''%'

AND T1.County like '%''[%2]''%'

Former Member
replied

Try this one:

SELECT T0.CardCode as 'BP Code',
 T0.CardName as 'BP Name', T0.CardType as 'Type', 
 T1.AdresType as 'Type', T1.Street, T1.Block,
 T1.City, T1.County, T1.ZipCode as 'Postcode', T1.Country 
FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode 
WHERE isnull(T1.ZipCode,'') like '%'+'[%0]'+'%' 
AND T1.City like '%'+'[%1]'+'%'
AND isnull(T1.County,'') like '%'+'[%2]'+'%'

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question