Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

in-DB Variable Input

Highlighted
6 - Meteoroid

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).

Highlighted
12 - Quasar

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.

 

 

Highlighted
6 - Meteoroid

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.

 

 

Highlighted
12 - Quasar

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]

 

Labels