March 23, 2004 at 6:02 am
I am trying to write a user defined function which translates functions written by the user in an easy to understand language (like xls) to queries SQL can execute.
Basically this function should convert something written as e.g.:
to a query.
Has anybody ever seen anything like this?
March 23, 2004 at 7:39 am
SOrry no I haven't seen anything like this. Where would the user be typing these and why don't you if in an app write a list of acceptable functions and such.
March 23, 2004 at 8:03 am
Sorry I did not get your question right, like do you want to write a script for displaying user with some info based on the data in excel sheet or something like that?
If you want to display user with some understandable format based on the data in the sql server columns then probably you may want to go with CASE .... WHEN ..... THEN ..... ELSE ..... END structure.
Sorry If understood it wrong.
Thanks
Prasad Bhogadi
www.inforaise.com
March 23, 2004 at 9:16 am
I just want the user to be able to write simple xls-look-alike functions which are stored in one column in a table (through an asp.net front end). In the end, the user clicks calculate and all of these expressions are changed to sql queries and calculated.
March 23, 2004 at 10:29 am
If your users can understand xls they probably can handle T-SQL. But have you considered MS English Query for the users? or else some visual SQL building tool.
Francis
March 24, 2004 at 1:16 pm
Ducati,
What you are trying to do is extremely complex and probably not possible without a thorough knowledge of excel's programming language. Problems like nesting become pretty stinkin' complex pretty quick. Now, If you wanted to give reduced functionality you could probably do it. Here's an example of how to parse an IF statement. This can't handle nested ifs, though, which makes it relatively useless. Also, any extra commas would throw it off (Like "IF(A= "lawson, calvin", 0, 1)" )
Have fun...
create function xls_to_SQL_IF(@Query varchar(8000))
returns varchar(8000)
as
begin
if cast(@Query as char(3)) <> 'if('
Begin
select @Query = 'Not an IF'
END
ELSE
BEGIN
select @Query = replace(@Query, 'if(', '')
declare @if varchar(3333),
@Then varchar(3333),
@Else varchar(3333)
--Build If Statement
select @if = substring(@Query, 0, charindex(',', @Query)),--pull out if statement
@Query = replace(@Query, @If + ',', '')--replace if statement with empty string
--Build Then Statement
select @Then = substring(@Query, 0, charindex(',', @Query)),--pull out then statement
@Query = replace(@Query, @Then + ',', '')--replace then statement with empty string
--Build Else Statement
select @Else = substring(@Query, 0, charindex(')', @Query)),--pull out Else statement
@Query = replace(@Query, @Else + ')', '')--replace then statement Else empty string
IF len(rtrim(ltrim(@Query))) <> 0
BEGIN
Select @Query = 'Parsing Error - extra characters:' + @Query
END
ELSE
BEGIN
select @Query = 'IF (' + @IF + ') BEGIN Select ' + @Then + ' END ' +
Case len(rtrim(ltrim(@Else)))
When 0 then ''
ELSE 'ELSE BEGIN Select ' + @Else + ' END'
END
END
END
return @Query
end
Signature is NULL
March 26, 2004 at 9:41 am
Hi Calvin,
First of all, thanks for the input. It is indeed the parsing which becomes quite complicated. I once created a vb-class which could handle expressions with simple one variable functions (like len, ...). There I also had problems with multiple-variable functions (like the if, mid, ...) and nested functions.
Major issue in this is figuring out where a function starts and what the different variables are (so basically it's all about parsing). Once I can split it up into these separate parts (which I did in an array in the vb-class), I can just pass these to separate user defined functions for each function (e.g. the if to a fn_if, the mid to a fn_mid, ...). That's how I see it at the moment. Any other comments are very much appreciated.
Ducati
March 26, 2004 at 10:50 am
As you stated Parsing is the hard part. Basically I would create a UDF to parse out each command and return them as a table. Then work from the inner most command out.
I recently did something similar for scanning our database. We created a custom syntax that would allow the user to enter a regular expression to search various fields in the database. I made a parser to parse the command in a UDF and then cycled through the commands creating dynamic sql to query the data. The final result was a standard recordset that produced the data for the user. This was to replace a C++ method that did the same thing but in a very poorly executed way. I ended up improving performance 10 fold over the C++ method.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 26, 2004 at 12:15 pm
Gary,
That's interesting....using a UDF that returns a table. I guess you could then use the output of the table udf to input to scalar udfs. Or something like that?
Hmmm....seems like someone should have already done this for XLS to SQL...there's probably something like this in .NET.
cl
Signature is NULL
March 27, 2004 at 2:49 am
So, a simple example focused on just one function (IF), IF(ColumnA = "Text1", 1, 0) + IF(ColumnB = "Text2", 1, 0) would be parsed on the + and then each of the expressions would be handled by a scalar if-udf. This should work.
Some early questions arise:
March 29, 2004 at 12:19 pm
Ducati,
That's what we're saying...that parsing the xls formulas is extremely hard, for exactly the questions you are asking, namely nesting and text delimiters. There has got to be a better way to do it, and there probably is (for a price).
Signature is NULL
March 29, 2004 at 3:15 pm
Definitely wouldn't do it in TSQL, absolute nightmare, no arrays, no easy looping contructs, no string comparison from a fixed point. Try in VB probably not as hard. lots of splits on ,+-=/*()
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply