Hi all,
I have this SQL query where I have a whereclause l.LineQuantity>six.TriggerQty
select c.CustomerAccountNumber, c.CustomerAccountName, h.DocumentNo, si.Code as ItemCode, si.[Name] as ItemName, pg.Code as ProductGroupCode, six.TriggerQty, l.LineQuantity, si.FreeStockQuantity, h.DocumentCreatedBy from SOPOrderReturn h inner join SOPORderReturnLine l on h.SOPOrderReturnID=l.SOPOrderReturnID inner join SLCustomerAccount c on h.CustomerID=c.SLCustomerAccountID inner join StockItem si on l.ItemCode=si.Code inner join StockItemX six on si.ItemID=six.StockItemXID inner join ProductGroup pg on si.ProductGroupID=pg.ProductGroupID where six.InStockAlert = 1 and six.TriggerQty>0 and l.LineQuantity>six.TriggerQty
I want to replicate the same in C# below.
SOPOrderReturns orders = new SOPOrderReturns(); orders.Joins.Add(new Sage.ObjectStore.Join("SOPOrderReturn", "SOPOrderReturnLine", "SOPOrderReturnID", "SOPOrderReturnID")); orders.Joins.Add(new Sage.ObjectStore.Join("SOPOrderReturn", "SLCustomerAccount", "CustomerID", "SLCustomerAccountID")); orders.Joins.Add(new Sage.ObjectStore.Join("SOPOrderReturnLine", "StockItem", "ItemCode", "Code")); orders.Joins.Add(new Sage.ObjectStore.Join("StockItem", "StockItemX", "ItemID", "StockItemXID")); orders.Joins.Add(new Sage.ObjectStore.Join("StockItem", "ProductGroup", "ProductGroupID", "ProductGroupID")); if(stockItem != null) { orders.Query.Filters.Add(new Sage.ObjectStore.Filter("[StockItem].[ItemID]", stockItem.Item)); } if(productGroup != null) { orders.Query.Filters.Add(new Sage.ObjectStore.Filter("[ProductGroup].[ProductGroupID]", productGroup.ProductGroup)); } if(analysisCodeValue != null) { orders.Query.Filters.Add(new Sage.ObjectStore.Filter("[stockItem].[AnalysisCode1]", analysisCodeValue.Value)); } orders.Query.Filters.Add(new Sage.ObjectStore.Filter("[StockItemX].[InStockAlert]", true)); orders.Query.Filters.Add(new Sage.ObjectStore.Filter("[StockItemX].[TriggerQty]", Sage.Common.Data.FilterOperator.GreaterThan, 0)); orders.Query.Filters.Add(new Sage.ObjectStore.Filter("[SOPOrderReturnLine].[LineQuantity]", Sage.Common.Data.FilterOperator.GreaterThan, "[StockItemX].[TriggerQty]")); // error happen here orders.Find();
However, I have error Sage.ObjectStore.DatabaseException: Error converting data type nvarchar to numeric. ---> System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.
I am guessing it try to convert [StockItemX].[TriggerQty] into numeric value.
How do I solve this to compare 2 columns value?
Thank you