Dynamic SQL statement in data source through scripting

Aaron Munoz · ‎07-29-2019

I want to create a SQL statement that uses two different datetime fields to pull delta records. I know there is a field called "Use last run datetime", but it can only use one field, and only imports records where the datetime field is between the last import and the lastest datetime in the chosen column. It would be something like this pseudo code:

IMPORT id
FROM my_table
WHERE (
(created_dt BETWEEN {ts gs.now()} AND {ts gs.now()})
OR
(approved_dt BETWEEN {ts gs.now()} AND {ts gs.now()})
)

the challenge is how to add a function like gs.now() inside the query that runs daily.

Manish Vinayak1 · ‎07-29-2019

I think the following documentation should be helpful:

https://docs.teradata.com/reader/kmuOwjp1zEYg98JsB8fu_A/W2ZJsPFeR7EFlyBUnQWrEw

https://teradata.github.io/presto/docs/0.167-t/functions/datetime.html

I haven't worked on Teradata SQL, but from looking at the documentation my guess would be:

SELECT *
FROM my_table
WHERE created > current_date - interval '2' day OR approved = current_date

View solution in original post

Aaron Munoz · ‎07-30-2019

Thanks for all the suggestions, specially to Manish. Even though this answer is outside what I was originally asked for, it is what I needed for my current use case.

Manish Vinayak1 · ‎07-30-2019

Glad that it helped 🙂

Cheers,

Manish