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