I'm embarrassed to say I've completely stumped myself with this. I assumed it would be simple for me, or that I'd be able to use some views around Content elements to work it out, but neither is the case.
In the way that, with Content elements, you can attach them to any level of the production element tree and they will flow downwards, but also be overruled by more specific rules, I'd built a configuration table like this:
title_no
prod_no
prod_season_no
perf_no
[configuration information]
I then expected to produce a view that just had
that I could match to transactions. The idea is that if I have
title_no = 1, config = 'A'
and
perf_no = 5, config = 'B'
And title_no 1 filters down to perf_nos 5-8 I would get
I'm seeing a couple of views that pull together the Content elements using unions, but no plan for choosing which configuration to use, which I guess is done in the application.
I think I may have you covered here -- a couple of years ago I built a set of functions and views that implement the inventory cascade and uploaded them to this snippet: https://bitbucket.org/TN_WebShare/workspace/snippets/qegqME
I think you may want dbo.LFT_PERF_CONTENT_FROM_TYPE(@content_type int), but there are a variety of different options available in that snippet.
Some of that looks like LV_INV_CONTENT, but the CTE parts (I haven't plunged into CTEs properly yet) got me thinking: Recursive CTEs! This is exactly the application they were designed for!
And then this happened.
Msg 465, Level 16, State 1, Line 21Recursive references are not allowed in subqueries.
And immediately after, this happened.
Msg 462, Level 16, State 1, Line 6Outer join is not allowed in the recursive part of a recursive common table expression 'overrides'.
I swear, it's like some evil and extremely methodical gnome has gone through TSQL functionality and ensured that any time I visualize a solution to one of my problems, there's an arcane exception ensuring that I can't, actually, do that.
Basically, my plan was to get all my perf-level configurations, then union in my production season level configurations, but skip any that would match to an already specified perf-level configuration. But I think without subqueries ("where not exists (") or outer joins ("left join...where p.perf_no is null") I don't think I can use previous entries as a screen against new matches.
Dammit, I was actually excited for a minute.
The next thing to try would be a four part union where each section would include a subquery that exhaustively duplicates all the previous queries in order to screen out the lower level configuration matches.
Gawain Lavers, That nasty gnome stops by here a bunch as well. :-)
Hope you are well.