[SQL] View to distill out the most specific rule for a configuration table using production elements

Just to make sure I am understanding your question correctly, are you saying that the perf_no level configuration would override the title_no configuration, or that it would be together?  Because, my understanding of how things like Content elements is that 5 would actually give you, Configuration:  "B,A", and not just "B".

Yes, it should override, I only want the most specific rule.

One option I've been trying to avoid because I think it's messy and fundamentally performance unfriendly is to use COALESCE, i.e.

COALESCE(perf.configuration, prod_season.configuration, prod.configuration, title.configuration)

But maybe that's my answer?  There's some ugliness there in that I have many columns of configuration data, and some might be null by design, but wouldn't want individual columns to come from different configuration.  I suppose what I could do is run the COALESE on the id of the configuration row, and then rejoin the table based on that...

Yeah, I think that might be your best way out.  At least, it is hard to say more without looking more specifically at the table(s) you are writing.

But, going the Content route where the table would be (inv_no,configuration_type,configuration_value), then, since, as I said above, Tessitura does not have a mechanism for prioritizing the configuration but simply returns all valid for a given perf_no (so "adding down" rather than "filtering down"), there is no mechanism to "copy" there, as it were, to get the specific configuration settings for that performance.

And I doubt that the performance using that form of a COALESCE would be THAT poor.  But again, hard to say from where I am sitting.

Gawain Lavers I am unsure if this will be helpful but I use a view that pulls a listing of customer performances that have specific keywords attached that determines whether is a particular performance valid for use in the view. The keywords can be attached at either of the 4 Production Elements levels with the Performance level having the highest priority coming back up to the Titles level having the lowest priority. The view makes use of multiple ISNULL() functions returning only the most specific keyword and may be a bit crude in hindsight but does what I need it to do with minimal execution time......Happy to share this view with you if relevant but just wanted to share what worked for me. Thanks! 

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.

Thanks Andrew: I'd love to at least scan it.

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 21
Recursive references are not allowed in subqueries.

And immediately after, this happened.

Msg 462, Level 16, State 1, Line 6
Outer 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.

Okay, imagine a configuration table, LTR_UCB_FEE_TKT_BEN_OVERRIDE which has:

id

type (fee, ticket, benevolent donation, only interested in the latter two for this)

fee_no (only for fees)

title_no

prod_no

prod_season_no

perf_no

price_type

[a bunch of columns with data for each configuration]

Only one production element level and price type pairing can be specified on a row (null okay for price type).  Basically if I had a configuration for a specific price type at the title level, I'd want to be able to specify another configuration set at, say, production season with "null" in the price type that would apply to all other price types for the relevant performances, and vice versa.

with pover as (
	select
		ov.type, p.perf_no, ov.price_type, ov.id
	from LTR_UCB_FEE_TKT_BEN_OVERRIDE as ov
		inner join T_PERF as p on p.perf_no = ov.perf_no
), psover as (
	select
		ov.type, p.perf_no, ov.price_type, ov.id
	from LTR_UCB_FEE_TKT_BEN_OVERRIDE as ov
		inner join T_PERF as p on p.prod_season_no = ov.prod_season_no
		left outer join pover as p1 on p1.perf_no = p.perf_no
			and p1.type = ov.type
			and (
				((ov.price_type is null) and (p1.price_type is null))
				or (ov.price_type = p1.price_type)
			)
	where p1.perf_no is null
), prover as (
	select
		ov.type, p.perf_no, ov.price_type, ov.id
	from LTR_UCB_FEE_TKT_BEN_OVERRIDE as ov
		inner join T_PROD_SEASON as ps on ps.prod_no = ov.prod_no
		inner join T_PERF as p on p.prod_season_no = ps.prod_season_no
		left outer join pover as p1 on p1.perf_no = p.perf_no
			and p1.type = ov.type
			and (
				((ov.price_type is null) and (p1.price_type is null))
				or (ov.price_type = p1.price_type)
			)
		left outer join psover as ps1 on ps1.perf_no = p.perf_no
			and ps1.type = ov.type
			and (
				((ov.price_type is null) and (ps1.price_type is null))
				or (ov.price_type = ps1.price_type)
			)
	where p1.perf_no is null
		and ps1.perf_no is null
), tover as (
	select
		ov.type, p.perf_no, ov.price_type, ov.id
	from LTR_UCB_FEE_TKT_BEN_OVERRIDE as ov
		inner join T_PRODUCTION as pr on pr.title_no = ov.title_no
		inner join T_PROD_SEASON as ps on ps.prod_no = pr.prod_no
		inner join T_PERF as p on p.prod_season_no = ps.prod_season_no
		left outer join pover as p1 on p1.perf_no = p.perf_no
			and p1.type = ov.type
			and (
				((ov.price_type is null) and (p1.price_type is null))
				or (ov.price_type = p1.price_type)
			)
		left outer join psover as ps1 on ps1.perf_no = p.perf_no
			and ps1.type = ov.type
			and (
				((ov.price_type is null) and (ps1.price_type is null))
				or (ov.price_type = ps1.price_type)
			)
		left outer join prover as pr1 on pr1.perf_no = p.perf_no
			and pr1.type = ov.type
			and (
				((ov.price_type is null) and (pr1.price_type is null))
				or (ov.price_type = pr1.price_type)
			)
	where p1.perf_no is null
		and ps1.perf_no is null
		and pr1.perf_no is null
)
select 'pover' as level, * from pover
union all
select 'psover' as level, ps.* from psover as ps
union all
select 'prover' as level, pr.* from prover as pr
union all
select 'tover' as level, t.* from tover as t

I am thinking now whether I would want instead to just do a bunch of left joins and use COALESCE to choose the id row in the configuration table.  I was just going to that back in to get the configuration columns, instead of having to spell them out in each row.

I see what you mean. Yeah generally I would agree that LEFT JOIN + COALESCE makes for the most straightforward implementation of a cascade/override pattern. Although you've got an extra layer of complexity going on with price type defaulting -- perhaps you would actually start with taking a cartesian product of your price types and inventory elements? Then you have a more straightforward join condition for overriding just a single price type.

At the risk of adding too many layers here, is your concern table complexity or performance when used?  From what I have heard thus far, your primary concern seems to have been performance.  That being said...

Since this is a custom configuration type thing, this might be a good argument for a comprehensive trigger which saves data to a sort of "currently valid configuration table", on which you then write your view.  That would allow the "work" of all the comprehensive COALESCing and LEFT JOINing and all that fun stuff to be completed at the time of saving, which would allow things like the T_PERF, T_PROD_SEASON, etc... tables to be able to be left alone during time of use so as not to tie up the database and hamper performance since your LTX_CURRENT_PERF_CONFIG would have the results of all that work already saved.  Sure, that table would be being updated regularly based on someone saving information to Titles, Productions, etc... but again, with all the "hard work" already out of the way, that would simplify your performance runtime.

Just a(nother) thought.

Nick Reilingh said:

Then you have a more straightforward join condition for overriding just a single price type.

Would that help me with the nulls?

That is a thought.  Performance is definitely my main concern, especially as this ("Override" is the clue) is supposed to be a rarely invoked part of a larger mapping structure, so this will simply embed into a much larger query.  For the moment, however, it's completing in milliseconds, so I think I can stick with my CTE solution for now.

I think so. Basically for each level of override, you would create a CTE (or temp table or subquery) that derives the full list of perf_no + price_type combinations that it applies to. (So in your derivation for title, you would join each row down to the perf level, and also join to price type ON title.price_type IS NULL OR title.price_type = pt.price_type.) Then you just LEFT JOIN all of the derivations to a "lookup" list of perf_no + price_type combos, and coalesce on the config column in priority order.

Just cause I know the people in this thread will find it interesting, it occurred to me that a completely different route you could decide to take would be to implement something akin to CSS specificity values. Calculate a specificity value for each row in the table based on which filters it does and doesn't specify, such that the more specific (overriding) rules are always valued higher than the less specific ones. Then if you want to lookup results from a table of production elements/price types, you create a JOIN condition with lots of ORs to join all rows from the rule table that can apply to the product/price type, apply a window function -- rank = ROW_NUMBER() OVER (PARTITION BY [product/price type] ORDER BY specificity DESC), and then do one more select where rank = 1... Maybe that results in fewer joins?

I thought briefly about PARTITION, but naively I expect those to be a bit slow.  The main issue I had with it, though, was that I couldn't wrap my head around assigning a specificity, but perhaps that would be doable with the master list of all price type/production element matrix you mentioned above.