This question may have been answered before, but I cannot seem to find exactly what I am looking for.
I am going to simplify this question down to two dimesions from 3, but I think the answer for the 2 dimesional case should apply directly to the 3 dimensional one.
If I have a table with X,Y values in it, and I have a function ROTATE(@X float, @Y float) which rotates the X,Y point to a new coordinate system and returns a table (Range as float, Angle as float), what is the best (most efficient way) to write a query which will present the input values for each row in my table along with the rotated values for each row?
I think that a select statement that looks like this:
SELECT t.X,t.Y, (SELECT RANGE from ROTATE(t.X,t.Y)) as Range, (SELECT Angle from ROTATE(t.X,t.Y)) as Range From Table as t;
will do the trick, however as you can see, it needs to call the ROTATE function twice to get the job done; and in a 3 dimesional case, this calculation is pretty expensive.
Is there anyway to only make the call once for each record in the Table?
Thanks for the help!