Tuesday, March 25, 2008

No anchor member was specified for recursive query *

I got this error while working on a recursive CTE query, I entered the error message in google/live and got zero results so I had to do some more study on CTEs and I thought I'd share the answer to this problem.

From the MS documentation we read:
The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.
CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.

Basically you cannot make a recursive call before you have defined the initial data set, let's see with an example:

This would be valid:

with example(col1, col2, iteration) as
(
--first, we defined the base set
select col1, col2 from someTable where col1 is null
union all
--then we can make the recursive call
select t1.col1, t1.col2
from example t1 inner join SomeTable t2 on t1.Col2 = t2.Col1
)
select * from example

and this would generate the error

with example(col1, col2, iteration) as
(
select t1.col1, t1.col2
from example t1 inner join SomeOtherTable t2 on t1.Col2 = t2.Col1
--ERROR:we have not defined a base set before making the recursive call
union all
select col1, col2 from someTable where col1 is null
)
select * from example

Hope this helps someone out there

Thursday, March 13, 2008

Do you not want to exit? yes, no, cancel, FileNotFound

This article refers to UI best practices

The title of the post is of course an exaggeration of asking negative questions but serves as a perfect example for what I'm trying to tell you
I thought the title of this post was an exageration, but after seeing that dialog...

Don't ask the user negative questions

More often than not you'll confuse that crap out of the poor user; users are already too scared of answering questions to still bother them with the opposite of what they want, it's a simple and basic rule, but I still see a lot of software (and developers) that use negative questions/options for data input.

The most common use of negative options is probably disabled

just compare:
Label X visible?
Field X Enabled?

To:
Label X Invisible?
Field X Disabled?

That subtle change makes it much harder to answer the question correctly; so prefer Enabled over Disabled, Visible over Invisible, Active over Inactive, etc

The same concept applies when naming variables or methods, in very few cases the negative is a better option, so just go with the safer option, it's easier to process, we are used to answer "positive questions" and the opposite usually causes us to think which makes things not intuitive