August 30, 2007 at 5:17 am
I don't think this is possible, however I will pose the question just in case....
I am converting a maths calculation engine into SQL. This is all OK so far, except when it comes to the use of aggregates. SQL uses aggregates on a set/table, however I need to do aggregates on columns in a single row. And there are differing numbers of columns that may be aggregated against. For instance :
Table Maths has 5 columns, fld1, fld2, fld3, fld4, fld5
I need to do something along the lines of :
SELECT AVG(fld1, fld2), AVG(fld3, fld4, fld5) FROM Maths
This is not possible with the built in AVG routine (AFAIK), so I had to write my own function fn_avg.
I was hoping to create a function with defaulted parameters - much the same as you can with a stored procedure - so that we do not need to supply all the parameters. e.g. the function could be :
CREATE FUNCTION fn_avg @fld1 int, @fld2 int, @fld3 int = NULL, @fld4 int = NULL
Then the above SELECT statement should work right.
Wrong.
In a quirk with SQL, you must supply the same number of parameters in the SQL call to the function as is defined in the function. So the statement has to read :
SELECT dbo.fn_avg(fld1, fld2, DEFAULT, DEFAULT), dbo.fn_avg(fld3, fld4, fld5, DEFAULT) FROM Maths
This is not really any good (I have end users to think about) as I am converting from an existing system, and really do not want to manually convert every aggregate function. My current solution is to have a separate function per number of parameters, so I have an fn_avg2 which accepts 2 parameters, fn_av3 which accepts 3 etc etc.
I have tried this using a CLR, and unfortunately it's the same issue in that you have to specify every parameter. I don't want any complicated rewriting of the maths either - can just about cope with a function name change but parsing the entire statement would be very difficult considering the complexity of the statements (lots of aerodynamic calculations).
Which is a big shame and should be an enhancement IMO.
Unless someone out there knows any different I am stuck with lots of functions, and have to hope my users can remember to use the correct one !!
August 30, 2007 at 5:57 am
on http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm there is a comprehensive article that describes the various ways to pass lists to stored procedures.
Regards,
Andras
August 30, 2007 at 6:45 am
Thanks for that - I'd already considered it but it would restrict the current usage and there are maths statements that it would not work for. I've over-simplified the example up there, but some of the maths is a lot more complicated. For instance (paraphrasing here !) :
SELECT AVG(POWER((fld2 - fld1), fld3), fld5, CASE WHEN fld6 = 1 THEN fld7 ELSE fld8 END)
This is possible if I have a AVG function as the POWER, CASE and fld contents are evaluated so only the values are passed thru to the function.
August 30, 2007 at 6:51 am
SELECT CASE
WHEN tCount12 = 0 THEN 0
ELSE 1.0 * tSum12 / tCount12
END AS Avg12,
CASE
WHEN tCount345 = 0 THEN 0
ELSE 1.0 * tSum345 / tCount345
END AS Avg345
FROM (
SELECT SUM(
ISNULL(fld1, 0)
+ ISNULL(fld2, 0)
) AS tSum12,
SUM(
CASE WHEN fld1 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN fld2 IS NULL THEN 0 ELSE 1 END
) AS tCount12,
SUM(
ISNULL(fld3, 0)
+ ISNULL(fld4, 0)
+ ISNULL(fld5, 0)
) AS tSum345,
SUM(
CASE WHEN fld3 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN fld4 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN fld5 IS NULL THEN 0 ELSE 1 END
) AS tCount345,
FROM Maths
) AS d
N 56°04'39.16"
E 12°55'05.25"
August 30, 2007 at 7:33 am
I've no problem writing the SQL from scratch, however the requirement for this is to replace a maths engine that a user edits. So the user enters statements that I can mostly execute in a select (or update) statement. There are a few that go beyond what the standard maths in SQL can do - i.e. the use of AVG, STDEV and MIN. The user will enter a statement such as :
MIN((fld1 - fld2), fld3, fld4)
This reads nice to them, and is possible if I convert slightly to read :
dbo.fn_min3((fld1 - fld2), fld3, fld4)
However, in order to cater for all of the possible scenarios, I need to create a new function per number of parameters. A little painful but it works. I was hoping that there would be a way of using the variable parameters (or default) in functions, however this requires the use of the keyword DEFAULT as a parameter placeholder which is not ideal.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply