So within the same database but different schemas I have two sets of tables.
Now I have a view defined that looks like this
A, B, C, D,
func('field1',A, B, C, D) E,
func('field2', A, B, C, D) F,
func('field3', A, B, C, D) G
func interrogates the second schema and performs queries using data values from the first schema as keys (A, B, C, D) to do effectively a kind of lookup.
I have about 15 of these derived columns where their value is from a lookup from another datasource (different schema, same database)
Now because of inconsistencies in the data these tables just aren't joined because if I did that I'd need a lot of conditional logic to control
the join depending on the values of A, B, C, D and so instead multiple cursors are sometimes walked in order to find the data to ensure I never have to worry about cartesian products.
It's done this way because the path to finding the data can vary or require different qualifiers so that code looks like this
field value = lookup data using one set of parameters
if no results
field value = lookup data using a different set of parameters
if no results
field value = lookup data using yet another different set of parameters
This means that each field is essentially a separate set of lookups but effectively all derived from the same row of data just different columns being returned. This is horribly inefficient as I should only need to get this row of data once and then use it to enrich the row of data I got from the first set of tables but there isn't really a join key between the two sets of data.
So, the goal would be to still make use a view but rather than do a separate call for each field I'd rather return multiple fields from the row at the same time.
I'm aware of TABLE views and the notion of parameterized views but what's not clear is if I join to the table returned how I relate that row data back to the original row
from the first set of tables which I assume I have to do in order to enrich A with B where values from A determine B and I'm expected to join A with B.