0

Hopefully the title makes some amount of sense. Basically I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue and theDate. Now assume I have a large table, called TableOfValue of values like so:

Values Date
1673 '2021-05-23'
1420 '2021-05-24'
-3273 '2021-05-25'
7231 '2021-05-26'
2331 '2021-05-27'

What I want to do is call my f_doCalc with the dates and values from all previous dates. So the first row would be called with a @MyUserDefinedTableType containing

theValue theDate
1673 '2021-05-23'

The second with a @MyUserDefinedTableType containing

theValue theDate
1673 '2021-05-23'
1420 '2021-05-24'

and so on.

Is this somehow possible to accomplish without using a WHILE loop? Such as of the form

SELECT Values, Date, f_doCalc( ... )
FROM TableOfValue
7
  • 1
    Please tag which database system and version this is for.
    – J.D.
    Jul 16 at 11:29
  • @McNets well that's my question. Currently Im just running a while loop and inserting the Relevant rows into the type table, running the function, and updating the main table, for each iteration. I'm wondering if there's a better way
    – Oscar
    Jul 16 at 13:09
  • @McNets For the above table example, 5 calculations done by f_doCalc() on first day 1 and corresponding value, then day 1 and day 2 with corresponding values, then day 1, day 2, day 3 etc... You can imagine f_doCalc() produces a running sum for example (in reality it's a much more complicated calculatino).
    – Oscar
    Jul 16 at 13:22
  • @McNets a value, it's a scalar function.
    – Oscar
    Jul 16 at 13:39
  • 1
    Sounds like you really want some kind of running sum, but without knowing what f_doCalc does, how can we say? You say below it involves IRR, perhaps stackoverflow.com/questions/12074638/… Jul 16 at 16:03
0

I'm pretty sure there is no way to create or manipulate table-type values in SELECT statements which is what you seem to want.

For the single row example you could create a wrapper function which takes the values you want in the table variable as individual arguments, creates a table variable containing them, then calls the existing function and forwards on the result to the caller. This would not be likely to be efficient though.

For the rolling list needed for the second and subsequent rows that would not directly work. You could have extra arguments for the function defining the start and end range from which to construct the content of the table variable, and use window functions to derive those values to pass in.

I expect this is not a good idea though, in terms of efficiency and potential locking issues. Perhaps you should ask a new question describing what you actually need the process to achieve overall? There may be a simpler way to get the result you are looking for.

2
  • Hi, thank you for your response.
    – Oscar
    Jul 16 at 15:13
  • I simply need to calculate values corresponding to each day. The calculation done is IRR (internal rate of return) which is an iterative procedure to find a value. I need to do this calculation each day with all the previous data available from the days up until that point. From your comment however it sounds like just creating a WHILE loop that at each iterations pulls all the data before a given date from the table and inserts it into the table type (and performs the calculation) is the way to go. I was just curious if there was a differnt approach I hadn't considered
    – Oscar
    Jul 16 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.