<QueryBody Provider="MSSQL">
SELECT
ART.ArticleNo [Number], ART.EANNo, ART.MainGroupNo, dbo.GetArticleText(ART.ArticleNo,@LanguageId,ART.Name) [Name],
ART.SubGroupNo, dbo.GetArticleUnitText(ART.ArticleNo,@LanguageId,ART.QuantityPerUnitTextSale) [UnitText],
ART.Weight+ART.PackingWeight [GrossWeight], ART.NumberOfDeliveryDays [DeliveryTime], ART.GrossPrice, ART.PackingWeight,
ART.Price1 [Price], ART.InActiveYesNo, ART.UtilityBits, ART.Volume, ART.IntermediateGroupNo [InterGroupNo],
ART.EUFiguresNo [EUGoodsStatisticsNo], ART.PurchasePacking, ART.QuantityPerUnitSale [UnitAmount],
MAI.Name [MainGroupName], SUB.Name [SubGroupName], ING.Name [IntermediateGroupName], WAR.Name [StockLocation],
ART.AltArtNo
FROM Article ART
LEFT OUTER JOIN MainGroup MAI ON MAI.[MainGroupNo] = ART.[MainGroupNo]
LEFT OUTER JOIN SubGroup SUB ON SUB.[SubGroupNo] = ART.[SubGroupNo]
LEFT OUTER JOIN IntermediateGroup ING ON ING.[IntermediateGroupNo] = ART.[IntermediateGroupNo]
LEFT OUTER JOIN WareHouse WAR ON WAR.[WareHouseNo] = ART.[WareHouseNo]
WHERE
ART.InActiveYesNo IN (0,3,6) AND ART.ShowOnWebYesNo = 1
</QueryBody>
In order to make the field searchable we have to add it to the pattern search. This is within the same query definition in the configuration file. Here I’ve added AltArtNo as a part of the search query.
<QueryParams>
<QueryParam Name="@LanguageId" SqlType="String" Description="LanguageCode" />
<QueryParam Name="SinglePatternSearch" Description="Search filter condition used when a single word is given">
$MainGroupNo > 0 AND (PATINDEX(@LikeSearch, $Name) > 0 OR PATINDEX(@LikeSearch, $Number) > 0) OR PATINDEX(@LikeSearch, $AltArtNo) > 0)
</QueryParam>
<QueryParam Name="FlexiPatternSearch" Description="Search filter condition when multiple words are given (max three)">
$MainGroupNo > 0 AND (PATINDEX(@LikeSearch, $Number) > 0 OR (PATINDEX(@SearchWord0, $Name) > 0 AND PATINDEX(@SearchWord1, $Name) > 0 AND PATINDEX(@SearchWord2, $Name) > 0) OR (PATINDEX(@SearchWord0, $AltArtNo) > 0 AND PATINDEX(@SearchWord1, $AltArtNo) > 0 AND PATINDEX(@SearchWord2, $AltArtNo) > 0))
</QueryParam>
<QueryParam Name="FieldPatternSearch" Description="Searches within a specific column">
PATINDEX(@SearchWord0, ${0}) > 0 AND PATINDEX(@SearchWord1, ${0}) > 0 AND PATINDEX(@SearchWord2, ${0}) > 0
</QueryParam>
</QueryParams>
