math between tables with no join

Thanks!  I may give this a shot as right now the “derived” table is rather large.  It’s functional, but I’m looking for something more efficient as once we get this working, we will be applying the same logic so far as the build of the procedures to a number of different areas in Tessitura.

Thanks all for the assist!

RJ

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Debbie Harland
Sent: Tuesday, March 03, 2015 7:08 AM
To: Richard Jackson
Subject: Re: [Tessitura Technical Forum] math between tables with no join

Hi Kevin,

You should be able to pick up the column name from sysobjects or syscolumns. They link on the id field.

Debbie

Hi Lisa –

I had thought about that route – and I’m using it on a couple of other tables that I tie to for math.  However, the number I’ll be doing the math on this will change (possibly) from 3-6 months and further – depending on the raw data – the actual variables that would be in that system table may change.  For example, one calculation looks at, say, column A.  However, depending on the outcome of the raw data, we may cease looking at column A and look now at column Z – so it would be a rather large system table with not all fields in use at the same time and I thought maybe that wasn’t the best use of a system table.  I could be wrong? It may make the proc as a whole more efficient?  I’ll give it a shot in our TEST system and see what I can find out.

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Lisa Lindvall
Sent: Wednesday, March 04, 2015 12:20 PM
To: Richard Jackson
Subject: Re: [Tessitura Technical Forum] math between tables with no join

Hi RJ,

Could you not create a local system table with the following columns?

id, tableA_field_name, tableB_field_name

Then you could create a view that joined something like this:

Select *

from

Table_A a

Join ltr_table_xref x on a.field_1 = x.tableA_field_name

join Table_B b on x.tableB_field_name = b.field1

You could create multiple views or CTEs joining on different entries in your local table depending on your needs.  If the calculation is always the same between fields, you could potentially add a formiula column to  your table as well:  ( (tableA_field1 * tableB field1)/100)  and use some dynamic sql to generate your calculated amounts.

In the example here, I added a row to tr_gooesoft_dropdown with a new code for testing purposes, the description field has my formula (cont_amt * 10).

declare @select_statement varchar(1000)

select @select_statement = 'select top 100 customer_no,cont_amt,' + description + ' as new_amt from t_contribution c' from tr_gooesoft_dropdown g where code = 1178

print @select_statement

exec (@select_statement)

You could do the same by substituting your join to the custom system table and using the formula field.  Then if formulas ever needed changing, you could do it in the table (1 location) rather than in all referenced views and/or procedures.

Best, 

lisa

If you took the table approach, you could just change the table entry from pointing at Column A to Column Z at any time.  If you used “inactive” columns, you could retain what you did historically.

I am not sure why it would need to be a large table, if you only needed to join via a few columns at a time.

Hi Lisa –

I had thought about that route – and I’m using it on a couple of other tables that I tie to for math.  However, the number I’ll be doing the math on this will change (possibly) from 3-6 months and further – depending on the raw data – the actual variables that would be in that system table may change.  For example, one calculation looks at, say, column A.  However, depending on the outcome of the raw data, we may cease looking at column A and look now at column Z – so it would be a rather large system table with not all fields in use at the same time and I thought maybe that wasn’t the best use of a system table.  I could be wrong? It may make the proc as a whole more efficient?  I’ll give it a shot in our TEST system and see what I can find out.

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Lisa Lindvall
Sent: Wednesday, March 04, 2015 12:20 PM
To: Richard Jackson
Subject: Re: [Tessitura Technical Forum] math between tables with no join

Hi RJ,

Could you not create a local system table with the following columns?

id, tableA_field_name, tableB_field_name

Then you could create a view that joined something like this:

Select *

from

Table_A a

Join ltr_table_xref x on a.field_1 = x.tableA_field_name

join Table_B b on x.tableB_field_name = b.field1

You could create multiple views or CTEs joining on different entries in your local table depending on your needs.  If the calculation is always the same between fields, you could potentially add a formiula column to  your table as well:  ( (tableA_field1 * tableB field1)/100)  and use some dynamic sql to generate your calculated amounts.

In the example here, I added a row to tr_gooesoft_dropdown with a new code for testing purposes, the description field has my formula (cont_amt * 10).

declare @select_statement varchar(1000)

select @select_statement = 'select top 100 customer_no,cont_amt,' + description + ' as new_amt from t_contribution c' from tr_gooesoft_dropdown g where code = 1178

print @select_statement

exec (@select_statement)

You could do the same by substituting your join to the custom system table and using the formula field.  Then if formulas ever needed changing, you could do it in the table (1 location) rather than in all referenced views and/or procedures.

Best, 

lisa