in-DB Variable Input

[DMH1]

I am trying to figure out how to use a variable in an in-DB flow to limit the rows returned from a very large SQL Server table. Quite simply, I only want a to read the rows with dates newer than a specified date key.

I thought of adding a small temporary table to the SQL Server DB that would contain this cutoff date. Unfortunately, I do not have write access to this database in my organization.

My next thought was to add the date to a small yxdb file and compare to that. That did not work.

Oracle table? Nope, not the same in-DB connection.

Is there some concept of a "global" variable that I could populate from a brief query and use in a formula?

HELP!! This should be really simple - but I'm struggling.
By the way, I can't have user input, because it will run automatically on a scheduled basis (I hope).

[wwatson]

What about putting a where statement into your sql? sysdate in Oracle sql = datetime right now

select field1, field2 , date from schema.table where date>=sysdate-365

or some variant of that.

[DMH1]

Thanks so much for getting back to me so fast!

I could certainly add that to the where statement, but the value is not usually the system date.  I read from a large revenue posting table that accumulates and grows daily.  I want to check the date that I last extracted data from this large "history" archive and look at rows that are newer than that date.  The revenue data is in a view of over 25 million rows, so processing time is non-trivial.

I could redo all of my stats from scratch each time I run, but then I would be hunted down by really angry DB Administrators.

[wwatson · Accepted solution]

if you use a dynamic in-db input tool you can edit the sql to insert an actual date

the dynamic in-db input takes 2 text columns as inputs

1. connection name

2. sql statement

you can build the sql statement using your workflow so you could calculate a date then insert it in a formula tool

calculated_date_field

'01-Jan-2020'

sql

"select fields from table where date>=" + [calculated_date_field]