Complex Query with Sage 200 function

SOLVED

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

  • +1
    verified 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.

  • 0 in reply to Chris Burke

    Thank you so much! I assume we can create the persistent object using Object Store Builder, correct? And I have to include the select query in the DBScript in sandbox.

  • 0 in reply to murni

    I tend do exactly that. create the view in SQL, create the object in objectstore builder that reflects the view, and then include the script to build the view, with a clause to remove the view if it exists.

  • 0 in reply to Toby

    noted. Thank you Toby