Complex Query with Sage 200 function

Question

I would like to do query as below: but I found that sage 200 only provide simple query & filter function. 
 This query is to get the TOP 1 of each group 
 select top 1 with ties m.MovementID, m.ItemID, m.CostPrice from MovementBalance m group by m.ItemID, m.CostPrice, m.MovementID order by ROW_NUMBER() over (partition by m.itemID order by m.ItemID, m.MovementID desc) 
 After I query above, I want to join it with Price Book Desktop List View and display the CostPrice value per stockItem & price band. Using Join function with table name=MovementBalance will not do because it will query all of its rows, whereas I only want to select rows with the latest movementID of each stockItem. 
 Is there alternative to do this? 
 I have look into Sage 200 ObjectStore Builder to create views from the select query, but it needed join which my query don't have. 
 
 Thank you

Chris Burke · Accepted Answer

Sage ObjectStore does support joins, but not windowing functions - so there's no way to express that select statement directly. 
 Here's how I would deal with it. First, create an actual view in the database from your select statement. We'll assume we've done that and it's called 'vw_TopCostPriceView'. 
 What you then need to do is hand-roll a View object and collection which maps onto that SQL view. It's a bit laborious - it's mostly boiler-plate code (and I create views like this so often that I've written code snippets in Visual Studio to do most of the work for me). We know that the MovementID column is going to be unique across the set that the view returns, so we can use that as our paging key. Your view and collection would look like this: 
 using Sage.Common.Data;
 using Sage.ObjectStore;
 using Sage.ObjectStore.CompoundKeys;

 [Sage.ObjectStore.Builder.PersistentObject(TableName = "vw_TopCostPriceView")]
 [CompoundKey(Name = "CK1", IsUnique = Unique.IsUnique, IsDefault = Default.IsDefault, IsIndexed = Indexed.IsIndexed)]
 [CompoundKeyField(FieldName = "MovementID", CompoundKeyName = "CK1", Precedence = 0)]
 public class TopCostPriceView : Sage.Common.DataAccess.View
 {
 public const string FIELD_MovementID = "MovementID";
 protected Sage.ObjectStore.Field _MovementID;

 [Sage.ObjectStore.Builder.MetaDataField(FieldPropertyName = "_MovementID", 
 DbType = System.Data.DbType.Int64,
 ColumnName = "MovementID")]
 public DbKey MovementID
 {
 get { return _MovementID.GetDbKey(); }
 }

 public const string FIELD_ItemID = "ItemID";
 protected Sage.ObjectStore.Field _ItemID;

 [Sage.ObjectStore.Builder.MetaDataField(FieldPropertyName = "_ItemID", 
 DbType = System.Data.DbType.Int64,
 ColumnName = "ItemID")]
 public DbKey ItemID
 {
 get { return _ItemID.GetDbKey(); }
 }

 public const string FIELD_CostPrice = "CostPrice";
 protected Sage.ObjectStore.Field _CostPrice;

 [Sage.ObjectStore.Builder.MetaDataField(FieldPropertyName = "_CostPrice", 
 DbType = System.Data.DbType.Decimal,
 ColumnName = "CostPrice")]
 public decimal CostPrice
 {
 get { return _CostPrice.GetDecimal(); }
 }
 }

 public class TopCostPriceViews : Sage.Common.DataAccess.ViewCollection
 {
 public TopCostPriceViews()
 {
 this.IsolationLevel = System.Data.IsolationLevel.ReadUncommitted;
 this.Query.AllowPaging = Sage.ObjectStore.AllowPagingType.AutoCompound;
 }

 public new TopCostPriceView this[int index] => (TopCostPriceView)base[index];
 public new TopCostPriceView First => (TopCostPriceView)base.First;
 public override PersistentObject Owner
 {
 get
 {
 if (this.Query.Owner == null)
 {
 this.Query.Owner = new TopCostPriceView();
 }
 return this.Query.Owner;
 }
 }
 } 
 Once you've created those objects they will behave like any other Object Store object : you can filter and sort the collection.

Sage 200 UKI

Complex Query with Sage 200 function

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING