Hello - I'm looking for a little advanced sql advice on a project i'm working on. Here's the lowdown:
I have two tables - we'll call them A and B. Table A has one row for every customer in the database and fifteen columns with a number in them specific to the customer. Table B has a hard coded set of decimal numbers that correspond to the columns of Table A. There is no common "join" between the two tables other than the column headers of Table A and a possible field in table B.
What I need to do is perform a rather lengthy math calculation using one field from table A multiplied to the variable in table B. It would be easy to do if I could join the tables together on something - but I'm not aware of a way to join tables from one field to another's column headers. I thought about pulling all the values into a temp table and doing the math from there, but the tables may change structure over time and according to other math formulas.
Is there a better way to get the math done than writing out, say, "select (tablea.field1 * tableb.field1)+(tablea.field2 * tableb.field2)+... on down the line?
Any advice or guidance is appreciated. Thanks!
RJ
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!
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
From: Kevin Madeira <bounce-kevinmadeira9286@tessituranetwork.com> Sent: 2/22/2015 8:32:55 PM
Hi Richard,
Are you not able to join Table B to another table which can then be joined to table A? For example:
Select A.SomeField, B.SomeField from A
Join C on A.SomeField = C.SomeField
Join B on C.SomeField = B.someField
and use this to create a SQL view so you can use that for referencing and calculations - it's then like working with just one table.
Cheers,
Kevin
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!