I'm deep into some interesting and fun worlds of mind-numbing complexity and that is the world of comprehending and interpreting business rules written in SQL. Along the way I saw something I haven't seen before. I'm sure it has an interesting name.
The basic idea is a kind of validation restraint on a wide query which is established by joining a narrow query. Cool.
Here's the construction in abstract...
select blah_one ... blah_n
from table_of_facts facts
(select distinct key_one, key_two, key_three
from (select distinct key_one, key_two, key_three
from validation_table
where key_three = constraint
UNION
select distinct key_one, key_two, key_three
from table_of_facts
where key_three = constraint) acceptable )
where
acceptable.key_one = fact.key_one and
acceptable.key_two = fact.key_two and
acceptable.key_three = fact.key_three
Now isn't that crazy? Now this thing was written for DB2, and that may be a really snazzy way to make your query run fast under that engine. But I swear in 15 years I have never seen that kind of construction, where you essentially join a table to itself and a validation table and run the constraint against the join rather than on the where. Still, I see how it can make sense when you are making nice ascii operations with blah_one through through blah_n.
I'll test it out one day when I have time.
Comments