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

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.

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.

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?

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.