Jiwa

Hi There

Am getting the attached error on a search option in version 7.2.0, using the below code, kindly advise

With JiwaApplication.Manager.Instance.Search
If .CurrentSearchMode = JiwaApplication.JiwaSearch.clsSearch.SearchModes.jswInventory Then

' Add a search to Inventory search options
Dim AOption As New JiwaApplication.JiwaSearch.SearchOption
With AOption
.Title = "Inventory LastCost with SoH"
.SQLStr = "SELECT dbo.IN_Main.InventoryID, dbo.IN_Main.PartNo, dbo.IN_Main.Description as 'PartNo', dbo.IN_Main.LCost, SUM(dbo.IN_SOH.QuantityLeft) as 'SoH' " +
"FROM dbo.IN_Main INNER JOIN " +
"dbo.IN_SOH On dbo.IN_Main.InventoryID = dbo.IN_SOH.InventoryID INNER JOIN " +
"dbo.IN_WarehouseSOH On dbo.IN_Main.InventoryID = dbo.IN_WarehouseSOH.InventoryID " +
"WHERE(dbo.IN_Main.Status <> 2) AND (dbo.IN_Main.Status <> 1) AND (dbo.IN_Main.Status <> 1) AND (dbo.IN_Main.Status <> 2) AND (dbo.IN_Main.BOMObject < 2) " +
"AND (IN_WarehouseSOH.IN_LogicalID = '" + JiwaApplication.Manager.Instance.Database.CurrentLogicalWarehouseID + "') " +
"GROUP BY dbo.IN_Main.InventoryID, dbo.IN_Main.PartNo, dbo.IN_Main.Description, dbo.IN_Main.LCost " +
.OrderBy = "ORDER BY IN_Main.PartNo"
.AddColumn ("InventoryID", vbString, "12", 0, 1)
.AddColumn ("Part No.", vbstring, "", 15, 2)
.AddColumn ("Item Description", vbString, "", 45,3)
.AddColumn ("LastCost", vbDecimal, "##,##0.00", 12, 4, , 2)
.AddColumn ("SoH", vbDecimal, "##,##0.00", 12, 5, , 2)

End With

.Options.Add(AOption)
End If

End With

Hi Mike / Scott

Any advise on this pls?

Off the top of my head I'd be focusing in on the GROUP BY clause. I cannot recall seeing any of our standard searches using GROUP BY and it's use may be screwing up the way Jiwa chops up the query into it's component parts.

Test this claim by eliminating the GROUP BY just to see if your search then works. If it turns out the GROUP BY *is* causing the problem, then turn your query into a view and make the search use the view instead.