February 1, 2008 at 8:02 am
I have a function defined that processes a rule. A rule has children that are either tests or rules. By definition, all rules must ultimately end with tests. Within the CTE of this function (attached), rule children are processed. If a rule child is a test, then the appropriate data is pulled from the test results table. However, rule children that are rules themselves must be processed by recursively calling the function again.
Therein, I believe lies the problem. The recursive function call is occuring within the definition of a CTE. Is the scope of the CTE local or global? If global, how can I make it local? A CURSOR has a similar problem, but it can be specified as local instead of being global by default.
I've been pulling out what's left of my hair, so any help would be greatly appreciated!!
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
February 1, 2008 at 9:25 am
A CTE is very local. In fact, it's only available to the SQL query that immediately follows it, although it can be used multiple times within that query. so you couldn't, for example, define a CTE and then run two seperate SELECT statements using that same CTE.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 1, 2008 at 9:35 am
I realize that it's quite local, but the problem I have is that within the CTE is a recursive call to the same UDF that has the CTE. Therefore, in this situation, the CTE is being defined again in the recursive call before it's been used in the parent call. Does that make sense?
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
February 1, 2008 at 9:36 am
Sorry - I saw the title "recursive UDF within a CTE" and got a very strong urge to leave the building before the server melts down...That's a "brave" combination there....
To follow up on Grant's comment - your Insert is out of scope. The select is only going to work against the SELECT immediately following the CTE statement. the INSERT is statement #2, so by then - there ain't no CTE in play anymore.
Meaning - it's very possible that the SELECT is correct, but it's not "surviving" long enough to be returned.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 1, 2008 at 9:50 am
I know that a CTE is only used in the following statement, but in this case I have 2 CTE's defined (the 1st feeding the 2nd). The INSERT statement is using the results from the 2nd CTE.
The problem is that in the 1st CTE there is a recursive function call. When I try to execute this, I get absolutely nothing. No errors, but then no results either.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
February 1, 2008 at 10:59 am
I must be having a slow day - but I keep getting lost in your logic. It just doesn't seem to leave any way "out" of the recursive function inside of a recursive CTE referenced by yet another recursive CTE. The CTE here just seems to make it HARDER to read.
Have you considered expanding the levels as you go, and keep replacing all of the rules with their children untill you have no further rules, and THEN "process" the rules to find the best one?
This would be done using a while construct instead of a recursive call.
It's a technique I've seen floating around called "expanding hierarchies" (I think Joe Celko coined the phrase a while back). The advantage of this reducing iterative process is that it doesn't have "scoping" issues, since there's only one scope level.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 1, 2008 at 12:24 pm
Thanks, I'll look into it. I did find that I can create a table of entries of all rule children (tests and rules) using a recursive CTE. That works just fine. I'll then have to figure out a way to loop through the rules and process them in order of level depth.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply