This project has moved. For the latest updates, please go here.

New Functions

Topics: Developer Forum
Feb 28, 2007 at 8:57 AM
Edited Feb 28, 2007 at 9:10 AM
Hi again,
I want to add new functions to NQuery. I added Microsoft.VisualBasic.Finacials namespace's some functions and it works. Like this .

FunctionBinding("DDB", IsDeterministic = true)
public static double DDB(double Cost, double Salvage,double Life,double Period,double Factor)
{
return Microsoft.VisualBasic.Financial.DDB(Cost, Salvage, Life, Period, Factor);
}
Thsi works.

But I can not add some functions. Can you give me signle exmaple how can I implement this.

Microsoft.VisualBasic.Financial.IRR(ref double[] values,double Guess)

Thanks.
Coordinator
Mar 3, 2007 at 1:38 PM
Edited Mar 4, 2007 at 11:48 AM
Hi,

Ref or out parameters are not supported by NQuery since SQL is generally side effect free. That means that you cannot call functions or methods that take a ref or out parameter. The only way around this is to create wrapper methods that don't take such parameters.

In your special case I have to say that I have no idea why the IRR method requires its first parameter to be passed by ref. According to .NET Reflector the IRR method does not assign a value to the parameter, so actually the ref modifier could be removed. That means you could create wrapper method(s) like this:

[FunctionBinding("IRR", IsDeterministic = true)]
public static double IRR(double[] values, double guess)
{
   return Microsoft.VisualBasic.Financial.IRR(ref values, guess);
}
 
[FunctionBinding("IRR", IsDeterministic = true)]
public static double IRR(double[] values)
{
  return Microsoft.VisualBasic.Financial.IRR(ref values, 0.1);
}

The second overloading is only needed to emulate the optional argument (which takes 0.1 as default).

However, I am not quite sure how you want to use this function. SQL has no syntax to declare and use arrays. The only way to mitigate this is to use parameters or constants that are initialized with an array. So you could use this function like this:

Query query = new Query();
query.DataContext.Functions.AddFromContainer(typeof(Program)); // Assuming the functions above are declared in class Program
query.Parameters.Add("@MyValues", typeof (double[]), new double[] {-1, 2, 3, 4, 5, 6});
query.Text = "SELECT IRR(@MyValues, 1)";
double irrResult = Convert.ToDouble(query.ExecuteScalar());

Is that what you want to achieve?
Mar 3, 2007 at 11:05 PM
Hi, Thanks for your reply.

I am using NQuery with Visual QueryBuilder control. In this case ref is not important. The Important point is , I have to run IRR or other functions with query clause. Ex:

I have datatable Orders with data. My sql query like this. "Select CustomerID,IRR(ShipVia,1) from Orders Group By CustomerId". NqUERY must group by customerID and send shipvia columns values to IRR function as an array.

This is very important for me. If question is not understood , please try to run query above succesfully.

Thank you verymuck again.
Coordinator
Mar 4, 2007 at 11:46 AM
Edited Mar 4, 2007 at 11:47 AM
Hi erguno,

as I understand you want to use this function like an aggregate. In this case you could create your own aggregate like this:

class Program
{
	public class IrrAggregate : AggregateBinding
	{
		private class IrrAggregator : IAggregator
		{
			private List<double> _valueList = new List<double>();
 
			public void Init()
			{
				_valueList.Clear();
			}
 
			public void Accumulate(object value)
			{
				_valueList.Add(Convert.ToDouble(value));
			}
 
			public object Terminate()
			{
				double[] allValues = _valueList.ToArray();
				return Financial.IRR(ref allValues, 1.0);
			}
 
			public Type ReturnType
			{
				get { return typeof(double); }
			}
		}
 
		public IrrAggregate(string name)
			: base(name)
		{
		}
 
		public override IAggregator CreateAggregator(Type inputType)
		{
			if (inputType == typeof(double) ||
				inputType == typeof(float) ||
				inputType == typeof(decimal) ||
				inputType == typeof(int) ||
				inputType == typeof(long))
				return new IrrAggregator();
 
			return null;
		}
	}
 
	static void Main()
	{
		DataSet dataSet = new DataSet();
		dataSet.ReadXml(@"K:\NQuery\Trunk\Etc\Sample Data\Northwind.xml");
		Query query = new Query();
		query.DataContext.AddTablesAndRelations(dataSet);
		query.DataContext.Aggregates.Add(new IrrAggregate("IRR"));
		query.Text = "SELECT CustomerID, IRR(ShipVia) FROM Orders GROUP BY CustomerId";
 
		DataTable dataTable = query.ExecuteDataTable();
		dataTable.WriteXml(Console.Out);
	}
}

Please note that aggregates can only take one parameter. So the guess parameter must be used implicitly by the aggreagte itself. That means that this parameter is fixed and cannot be changed inside the query.

Does this help?
Mar 4, 2007 at 1:32 PM
Very helpfull!. Thanks. But I have to send second parameter (1) from query also. How can I solve this. ? Thank you very much.
Coordinator
Mar 4, 2007 at 4:36 PM

erguno wrote:
But I have to send second parameter (1) from query also. How can I solve this.

Sorry, aggregates with more than parameter are not supported.

After reading the MSDN documentation to Financial.IRR method, I come to the conclusion that it is currently not easily possible to correctly support this function at all:

MSDN:
The IRR function uses the order of values within the array to interpret the order of payments and receipts. Be sure to enter your payment and receipt values in the correct sequence.

The example I provided uses GROUP BY and aggregation. In this case, all rows are sorted by CustomerID; the order of ShipVia is arbitrary. Unfortunately, you cannot enforce any order using ORDER BY since the ORDER BY operation might be moved by the optimizer so that it is evaluated before or after grouping. That means that aggregate functions must be invariant to sort orders.

This messages decribes how one could implement IRR in SQL Server: Google Groupes Archive. But since real scripting is out of scope for NQuery one must find a solution that uses plain SELECT statements. But even with SQL Server 2005 and custom aggregates this gets quite interesting:

/* SQL Server 2005 */
SELECT  CustomerId,
        (
            SELECT  IRR(ShipVia) -- This assumes IRR is a custom aggregate
            FROM    (
                        SELECT  TOP 100 PERCENT
                                o.ShipVia
                        FROM    Orders o
                        WHERE   o.CustomerId = outerRow.CustomerId
                        ORDER   BY o.ShipVia -- or whatsoever the correct sort order is
                    ) AS D
        ) IRR_Value
FROM    Orders outerRow
This query uses the TOP 100 PERCENT ... ORDER BY hack to enforce a designated sort order in the subquery (since ORDER BY is generally not supported). But I doubt that this looks like a solution - after all, the guess parameter must still be passed "behind the scenes" and the query looks quite messy. But it even gets worse: if you look at the execution plan you will find that the clause ORDER BY o.ShipVia has been dropped by the optimizer. The only way to get around this is to use a custom function that manually iterates over the tables using T-SQL scripting (as suggested in the usenet message mentioned above).

I guess what you really want to do is something like:

/* Pseudo SQL */
SELECT  CustomerId,
        IRR(
            (SELECT  o.ShipVia
             FROM    Orders o
             WHERE   o.CustomerId = outerRow.CustomerId
             ORDER   BY o.ShipVia -- or whatsoever the correct sort order is
            ), 1.0
        ) AS IrrValue
FROM    Orders outerRow
Since sub selects are still under development NQuery currently does not support this. In addition, sub selects as arguments to functions are not even planned. I am not even sure if this can be consistently implemented. However, I will consider this.

If you find a different work around or have another idea please let me know. I will try to help you as much as I can.

I am using NQuery with Visual QueryBuilder control.

By the way, which query builder do you use? Is that this one activequerybuilder?

Sorry for the long answer but you found a quite interesting problem :-)
Mar 4, 2007 at 5:24 PM
Thanks again. I am using BXQuery for Query Designer. BXQuery is more advanced and consistent from ActiveQueryBuilder. I understand the problem. What is your aprox. time plan for subselects. This is not urgent. You can answer later. We are using NQuery in a very big project and NQuery is one of the corecomponents. Because of this we are looking for changes every day.

If you need help on any subject , please email me.

Ergün Özyurt.
CEO
Coordinator
Mar 6, 2007 at 12:59 AM

Thanks again. I am using BXQuery for Query Designer. BXQuery is more advanced and consistent from ActiveQueryBuilder.

Thanks. I will have a look at it.

I understand the problem. What is your aprox. time plan for subselects.

As you can see, most of the critical issues related to subqueries have been closed this weekend. However, some important issues related to queries contained in CASE expressions are not implemented yet. This has both an impact on performance and correctness.

Unfortunetly, I am in the middle of preparation for an important exam at university. That means that I will not be able to contribute to this project for the next three weeks but I am very confident that I will be able to ship a beta release for NQuery incl. subqueries at the beginning of April.

However, as I already I said I am not sure if subqueries will help you to solve the problems related to calculation of IRR. Hence I would suggest that you try encapsulate the logic including reading the data in a custom function e.g.

    public class IrrCalculator
    {
        private DataContext _context;
 
        public IrrCalculator(DataContext context)
        {
            _context = context;
        }
        
        [FunctionBinding("IRR", IsDeterministic = true)]
        public double IRR(string customerId, double guess)
        {
            Query query = new Query();
            query.DataContext = _context;
            query.Text = @"
                SELECT  o.ShipVia
                FROM    Orders o
                WHERE   o.CustomerId = @CustomerId
                ORDER   BY o.ShipVia
            ";
            query.Parameters.Add("@CustomerId", typeof(string), customerId);
 
            List<double> valueList = new List<double>();
            foreach (DataRow row in query.ExecuteDataTable().Rows)
            {
                double value = Convert.ToDouble(row[0]);
                valueList.Add(value);
            }
 
            return Financial.IRR(ref allValues, guess);
        }
    }
    
    public static class Program
    {
        public static void Main()
        {
            DataSet dataSet = new DataSet();
            dataSet.ReadXml(@"K:\NQuery\Trunk\Etc\Sample Data\Northwind.xml");
 
            Query query = new Query();
            IrrCalculator calculator = new IrrCalculator(query.DataContext);
            query.DataContext.Functions.AddFromContainer(calculator);
            query.DataContext.AddTablesAndRelations(dataSet);
            query.Text = @"
                SELECT CustomerID,
                       IRR(CustomerID, 1.0) AS IrrValue
                FROM Customers
            ";
 
            DataTable dataTable = query.ExecuteDataTable();
            dataTable.TableName = "Result";
            dataTable.WriteXml(Console.Out);
        }
    }

Of course, this does not have the best performance possible and is not as flexible as the subselect but it could work in the meantime. Hope this helps you.


We are using NQuery in a very big project and NQuery is one of the corecomponents. Because of this we are looking for changes every day.


Great to hear that :-)