cancel
Showing results for 
Search instead for 
Did you mean: 

Combining variables in a query

Former Member
0 Kudos

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]''%'

Accepted Solutions (1)

Accepted Solutions (1)

former_member204969
Active Contributor
0 Kudos

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]'+'%'

Former Member
0 Kudos

Yes that works thank you very much. Any thoughts on why I was having problems? This 'isnull' is new to me.

Ralph

former_member204969
Active Contributor
0 Kudos

The 'isnull' function retrieves the field value when it is not null, otherwise the second parameter. You need it because the SQL assumes the condition invalid when it refer to a null value.

Former Member
0 Kudos

The strange thing is that my original version gave results for a city search where the county was filled in or empty (null). There did not appear to be logic behind which BP's it showed and which it ignored.

Thanks anyway

Answers (0)