Use function/parameters stored in columns

Question

I'm not sure this question has already been asked, if so I didn't find it.

For a log project we used table to parametrize our data loading run and get track of their basic configuration.

On the specific side of the pull date we also wanna have the configuration in the table and so we putted three information in it: the base date / the range we use to calculate pull from and pull to timestamps.

My issue here is I wanna call the DATEADD() function using those columns as parameters but I don't know how to unstring those values as 'CURRENT_TIMESTAMP' will not work, just like DATEADD('year',...) will also raise an error cause the parameter should not be a string.

To summ up, from values ['CURRENT_TIMESTAMP', 'year', '-6'] in base I wanna create the call DATEADD(year, -6, CURRENT_TIMESTAMP)

Thanks

Screen of my table

Simeon Pilgrim · Accepted Answer · 2022-02-08 19:30:24Z

1

Make a case statement, and just match the two columns to there values. Remember case stop on the first in order match.

CASE 
    WHEN col1 = ‘CURRENT_TOMESTAMP’’ AND col2 = ‘year’ 
        THEN DATEADD(year, col3, CURRENT_TIMESTAMP)
    WHEN col1 = ‘CURRENT_TOMESTAMP’’ AND col2 = ‘month’ 
        THEN DATEADD(month, col3, CURRENT_TIMESTAMP)
...

Etc etc

edited Feb 8, 2022 at 19:30

answered Feb 8, 2022 at 12:12

Simeon Pilgrim

26.7k3 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Clément LEDUC Over a year ago

Yeah this is the solution I implemented but I was wondering if we can somehow directly make a generic call

Marcin Zukowski Over a year ago

You could use stored procedures and compose dynamic strings to be evaluated, but the per-record overhead will be quite high, so it's probably only efficient for small datasets.

Simeon Pilgrim Over a year ago

@MarcinZukowski Yip, I was going to suggest that, but it's along the same lines as do all your ELT via INSERT commands. for tiny SQL "it just works (tm)" for real data sizes, your compile times will be longer than your execution times (speaking from a lift and shift experience)

Collectives™ on Stack Overflow

Use function/parameters stored in columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related