Hi, I need to convert the following SQL Query to LLBLGen Code.

i use LLB 2.6/ORACLE 11

SELECT T.ITEM_CODE,T.PRICEVALUE,T.EFFDATECHAR, RANK() OVER(PARTITION BY T.ITEM_CODE ORDER BY T.EFFDATECHAR DESC) RK FROM PRICEBOOK T ORDER BY T.ITEM_CODE,T.EFFDATECHAR DESC

This has been asked before but in a Help desk thread. I'll try to copy the information from there.

user wrote:

How can I take advantage of the SQL 2005 features of RANK() & Row_Number() without using views or stored procedures? I am simply trying to add a coulmn to the entity of 'position'. select *, RANK() over (order by myDate) from myTable

myself wrote:

You should implement IExpression as in the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829 Then you should use a DynamicList to fetch the needed data. And use the SetExpression() method to set an instance of the IExpression class you have implemented to one of the ResultSet fields.

user wrote:

Thanks. Works great. Heres the code for your interest: [Serializable] public class RowNumberExpression : Expression { public RowNumberExpression(IEntityField2 field): base(field) { }

    public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        return string.Format("ROW_NUMBER() OVER (order by {0})", base.ToQueryText(ref uniqueMarker, inHavingClause));
    }
}

Hope this is helpful.